﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DTTB.MV2.QUANLYNHANSUTIENLUONG.Models.LinQ.TinhLuong;
using DTTB.MV2.QUANLYNHANSUTIENLUONG.Models.FormBean.TinhLuongModel;
using DTTB.DTO;
using DTTB.MV2.QUANLYNHANSUTIENLUONG.BussinessDelegate.ChamCongDelegate;
using Webdiyer.WebControls.Mvc;
namespace DTTB.MV2.QUANLYNHANSUTIENLUONG.BussinessDelegate.TinhLuong
{
    public class TinhLuongHangThangServiceDelegate
    {
        #region ghi chú
        // hiện tại có đến 3 phần tính lương chi tiết cho nhân viên. phần 1 là 1 nhân viên, phần 2 là lúc inset phần 3 là lúc update. nếu sửa công thức tính lương
        // hay sửa lại việc tính lương thì phải sửa cả 3 phần trên
        #endregion

        #region Private Member
        DBTinhLuongDataContext _DBTinhLuong = new DBTinhLuongDataContext();
        ChamCongServiceDelegate _ChamCongServiceDelegate = new ChamCongServiceDelegate();
        #endregion

        #region Get

        #region tb_danhsachnhanvien
        public IQueryable<tb_DanhSachBangTinhLuong> GetAll()
        {
            return _DBTinhLuong.tb_DanhSachBangTinhLuongs.Where(p =>p.IsDelete == null || p.IsDelete == false).OrderByDescending(p => p.NgayTao);
        }

        public PagedList<tb_DanhSachBangTinhLuong> GetDanhSachTinhLuongPagedList(int? Pagednumber)
        {
            return this.GetAll().ToPagedList(Pagednumber ?? 1, Common.CommonVariables.DefaultNumberOfDay);
        }

        public tb_DanhSachBangTinhLuong GetDanhSachTinhLuongById(int id)
        {
            return _DBTinhLuong.tb_DanhSachBangTinhLuongs.SingleOrDefault(p => p.ID == id && (p.IsDelete == null || p.IsDelete == false));
        }
        public tb_DanhSachBangTinhLuong GetDanhSachTinhLuongByThangNam(int thang, int nam)
        {
            try
            {
                return _DBTinhLuong.tb_DanhSachBangTinhLuongs.SingleOrDefault(p => p.Thang == thang && p.Nam == nam && (p.IsDelete == null || p.IsDelete == false));
            }
            catch (Exception) { return null; }
        }
        #endregion

        public hs_NhanVien GetNhanVienById(int? idNhanVien)
        {
            try
            {
                return _DBTinhLuong.hs_NhanViens.Single(p => p.Id == idNhanVien);
            }
            catch (Exception)
            {
                return null;
            }
        }

        public bool CheckExistThangNam(string thang, string nam)
        {
            tb_DanhSachBangTinhLuong danhsachbangtinhluong = this.GetDanhSachTinhLuongByThangNam(Convert.ToInt32(thang), Convert.ToInt32(nam));
            if (danhsachbangtinhluong != null)
                return false;
            return true;   
        }

        public bool CheckExistLuongChiTietThangNam(int id, string thang, string nam)
        {
            return _DBTinhLuong.tb_TinhLuongThangChiTiets.Where(p => p.IdBangTinhLuong == id && p.ThangTinhLuong == Convert.ToInt32(thang) && p.NamTinhLuong == Convert.ToInt32(nam)).Any();
        }

        #region tb_TinhLuongThangChiTiet
        public tb_TinhLuongThangChiTiet GetTinhLuongThangChiTiet(int id)
        {
            return _DBTinhLuong.tb_TinhLuongThangChiTiets.SingleOrDefault(p => p.Id == id && (p.IsDelete == null || p.IsDelete == false));
        }
        #endregion
        #endregion

        #region Set
        public tb_DanhSachBangTinhLuong InsertDanhSachTinhLuong(tb_DanhSachBangTinhLuong obj)
        {
            try 
            {
                _DBTinhLuong.tb_DanhSachBangTinhLuongs.InsertOnSubmit(obj);
                _DBTinhLuong.SubmitChanges();
                return obj;
            }
            catch (Exception) { return null; }
        }

        #region Update
        
        public tb_DanhSachBangTinhLuong UpdateDanhSachTinhLuong(LapBangTinhLuongForm form)
        {
            tb_DanhSachBangTinhLuong obj = this.GetDanhSachTinhLuongById(form.Id);
            if (obj == null || obj.IsLock || obj.IsFinish)
                return null;
            obj.Thang = form.Thang;
            obj.Nam = form.Nam;
            obj.HoTenNguoiLapBieu = form.HoTenNguoiLapBieu;
            obj.HoTenNguoiKy = form.HoTenNguoiKy;
            obj.ChucVuNguoiKy = form.ChucVuNguoiKy;
            if(!string.IsNullOrEmpty(form.NgayKy))
                obj.NgayKy =Common.DateTimeExtension.ConvertStringDDMMYYYYToYYYYMMDD(form.NgayKy);
            obj.GhiChu = form.GhiChu;
            obj.NguoiSua = Convert.ToInt32(HttpContext.Current.Session["USERID"]);
            obj.NgaySua = DateTime.Now;
            _DBTinhLuong.SubmitChanges();
            return obj;
        }

        public string[] SuaTinhLuongTungNhanVien(int id, string str)
        {
            try
            {
                if (!string.IsNullOrEmpty(str))
                {

                    str = str.Substring(0, str.Length - 1);
                    string[] colums = str.Split(',');
                    tb_TinhLuongThangChiTiet obj = this.GetTinhLuongThangChiTiet(id);

                    #region update
                    for (int i = 0; i < colums.Length; i++)
                    {

                        string[] IdvsValue = colums[i].Split('=');
                        if (IdvsValue[0].Equals("SoTienNguoiPhuThuoc"))
                        {
                            IdvsValue[1] = IdvsValue[1].Replace(".", "");
                            obj.SoTienNguoiPhuThuoc = Convert.ToDecimal(IdvsValue[1]);
                        }
                        else if (IdvsValue[0].Equals("SoTienThuongKmCongNhat"))
                        {
                            IdvsValue[1] = IdvsValue[1].Replace(".", "");
                            obj.SoTienThuongKmCongNhat = Convert.ToDecimal(IdvsValue[1]); 
                        }
                        else if (IdvsValue[0].Equals("SoTienThuongHieuQuaCongViec"))
                        {
                            IdvsValue[1] = IdvsValue[1].Replace(".", "");
                            obj.SoTienThuongHieuQuaCongViec = Convert.ToDecimal(IdvsValue[1]);
                        }
                        else if (IdvsValue[0].Equals("DieuChinh"))
                        {
                            IdvsValue[1] = IdvsValue[1].Replace(".", "");
                            obj.DieuChinh = Convert.ToDecimal(IdvsValue[1]); 
                        }
                        else if (IdvsValue[0].Equals("TienDongBaoHiemXaHoi"))
                        {
                            IdvsValue[1] = IdvsValue[1].Replace(".", "");
                            obj.TienDongBaoHiemXaHoi = Convert.ToDecimal(IdvsValue[1]);
                        }
                        else if (IdvsValue[0].Equals("TienDongBaoHiemYTe"))
                        {
                            IdvsValue[1] = IdvsValue[1].Replace(".", "");
                            obj.TienDongBaoHiemYTe = Convert.ToDecimal(IdvsValue[1]);
                        }
                        else if (IdvsValue[0].Equals("NgayCongLamViec"))
                        {
                            obj.NgayCongLamViec = Convert.ToDecimal(IdvsValue[1]);
                        }
                        else if (IdvsValue[0].Equals("NgayTinhPhuCap"))
                        {
                            obj.NgayTinhPhuCap = Convert.ToDecimal(IdvsValue[1]);
                        }
                        else if (IdvsValue[0].Equals("NghiLeNghiPhepCheDo"))
                        {
                            obj.NghiLeNghiPhepCheDo = Convert.ToInt32(IdvsValue[1]);
                        }
                        
                    }
                    #endregion

                    #region tính toán lại dữ liệu

                    #region khai báo biến
                    decimal tongngaylamviec, luongcanban, tienmotngaycong, LuongCongNhat, songayhuongcacphucap, PCTrachNhiemCongNhat, tienchuyencan, ChuyenCanCongNhat, PhuCapDiLaiCongNhat;
                    decimal phucaptiencom, PhuCapTienComCongNhat, phucapdilai, phucaptrachnhiem, phucapdienthoai, PhuCapDienThoaiCongNhat;
                    decimal TienNghiLeNghiPhepCheDo, SoTienThuongKmCongNhat, SoTienThuongChuyenCongNhat, SoTienThuongHieuQuaCongViec, DieuChinh, TienDongBaoHiemYTe, TienDongBaoHiemXaHoi, TienDongBaoHiemThatNghiep;
                    decimal TienTamUng, TienHuongNgungLamViec, TroCapThoiViec, TienPhiCongDoan, LuongTruocThue, TienThueThuNhap, tienthuclanh, tongtiennhanduoc, SoTienNguoiPhuThuoc;
                    #endregion
                    // tiền người phụ thuộc
                    SoTienNguoiPhuThuoc = obj.SoTienNguoiPhuThuoc != null ? obj.SoTienNguoiPhuThuoc.Value : 0;

                    //luong ban bản
                    tongngaylamviec = obj.NgayCongLamViec != null ? obj.NgayCongLamViec.Value : 0;
                    luongcanban = obj.LuongCanBan != null ? obj.LuongCanBan.Value : 0;
                    tienmotngaycong = luongcanban / 26;
                    LuongCongNhat = (tienmotngaycong * tongngaylamviec);
                    obj.LuongCongNhat = LuongCongNhat;//tinhLuongChiTiet.LuongCanBan;

                    songayhuongcacphucap = obj.NgayTinhPhuCap != null ? obj.NgayTinhPhuCap.Value : 0;
                    //trach nhiem
                    phucaptrachnhiem = obj.PhucCapTrachNhiem != null ? obj.PhucCapTrachNhiem.Value : 0;
                    PCTrachNhiemCongNhat = ((phucaptrachnhiem / 26) * songayhuongcacphucap);
                    obj.PCTrachNhiemCongNhat = PCTrachNhiemCongNhat;//tinhLuongChiTiet.PhucCapTrachNhiem;
                    //chuyen cần
                    tienchuyencan = obj.ChuyenCan != null ? obj.ChuyenCan.Value : 0;
                    ChuyenCanCongNhat = ((tienchuyencan / 26) * songayhuongcacphucap);
                    obj.ChuyenCanCongNhat = ChuyenCanCongNhat;// tinhLuongChiTiet.ChuyenCan;
                    // đi lại
                    phucapdilai = obj.PhuCapDiLai != null ? obj.PhuCapDiLai.Value : 0;
                    PhuCapDiLaiCongNhat = ((phucapdilai / 26) * songayhuongcacphucap);
                    obj.PhuCapDiLaiCongNhat = PhuCapDiLaiCongNhat; //tinhLuongChiTiet.PhuCapDiLai;
                    //tiền cơm
                    phucaptiencom = obj.PhuCapTienCom != null ? obj.PhuCapTienCom.Value : 0;
                    PhuCapTienComCongNhat = ((phucaptiencom / 26) * songayhuongcacphucap);
                    obj.PhuCapTienComCongNhat = PhuCapTienComCongNhat;//tinhLuongChiTiet.PhuCapTienCom;
                    //tiền điện thoại
                    phucapdienthoai = obj.PhuCapDienThoai != null ? obj.PhuCapDienThoai.Value : 0;
                    PhuCapDienThoaiCongNhat = ((phucapdienthoai / 26) * songayhuongcacphucap);
                    obj.PhuCapDienThoaiCongNhat = PhuCapDienThoaiCongNhat;//tinhLuongChiTiet.PhuCapDienThoai;
                    // nghỉ phép chế độ
                    int songaynghiphepnamchedoi = obj.NghiLeNghiPhepCheDo != null ? obj.NghiLeNghiPhepCheDo.Value : 0;
                    TienNghiLeNghiPhepCheDo = this.GetTienNghiLe(songaynghiphepnamchedoi, tienmotngaycong);
                    obj.TienNghiLeNghiPhepCheDo = TienNghiLeNghiPhepCheDo;
                    //end
                    //ngừng làm việc
                    TienHuongNgungLamViec = obj.TienHuongNgungLamViec != null ? obj.TienHuongNgungLamViec.Value : 0;
                    TroCapThoiViec = obj.TroCapThoiViec!=null?obj.TroCapThoiViec.Value:0;
                    SoTienThuongKmCongNhat = obj.SoTienThuongKmCongNhat!=null?obj.SoTienThuongKmCongNhat.Value:0;
                    SoTienThuongChuyenCongNhat = obj.SoTienThuongChuyenCongNhat!=null?obj.SoTienThuongChuyenCongNhat.Value:0;
                    SoTienThuongHieuQuaCongViec = obj.SoTienThuongHieuQuaCongViec!=null?obj.SoTienThuongHieuQuaCongViec.Value:0;
                    DieuChinh = obj.DieuChinh!=null?obj.DieuChinh.Value:0;
                    //bảo hiểm
                    dm_BaoHiem BaoHiem = this.BaoHiem();
                    decimal PhanTramBHYT = BaoHiem != null ? BaoHiem.BHYT.Value : 0;
                    decimal PhanTramBHXH = BaoHiem != null ? BaoHiem.BHXH.Value : 0;
                    decimal PhanTramBHTN = BaoHiem != null ? BaoHiem.BHTN.Value : 0;
                    TienDongBaoHiemYTe = luongcanban * PhanTramBHYT;//obj.TienDongBaoHiemYTe!=null?obj.TienDongBaoHiemYTe.Value:0;
                    TienDongBaoHiemXaHoi = luongcanban * PhanTramBHXH; //obj.TienDongBaoHiemXaHoi!=null?obj.TienDongBaoHiemXaHoi.Value:0;
                    TienDongBaoHiemThatNghiep = luongcanban * PhanTramBHTN; //obj.TienDongBaoHiemThatNghiep!=null?obj.TienDongBaoHiemThatNghiep.Value:0;
                    obj.TienDongBaoHiemYTe = TienDongBaoHiemYTe;
                    obj.TienDongBaoHiemXaHoi = TienDongBaoHiemXaHoi;
                    obj.TienDongBaoHiemThatNghiep = TienDongBaoHiemThatNghiep;
                    //end
                    TienPhiCongDoan = obj.TienPhiCongDoan!=null?obj.TienPhiCongDoan.Value:0;
                    TienTamUng = obj.TienTamUng != null ? obj.TienTamUng.Value : 0;
                    //thu nhập trước thuế
                    LuongTruocThue = (LuongCongNhat + PCTrachNhiemCongNhat + ChuyenCanCongNhat + PhuCapDiLaiCongNhat + PhuCapTienComCongNhat + PhuCapDienThoaiCongNhat + TienNghiLeNghiPhepCheDo + TienHuongNgungLamViec + TroCapThoiViec + SoTienThuongKmCongNhat + SoTienThuongChuyenCongNhat + SoTienThuongHieuQuaCongViec + DieuChinh) - (TienDongBaoHiemYTe + TienDongBaoHiemXaHoi + TienDongBaoHiemThatNghiep + TienPhiCongDoan + SoTienNguoiPhuThuoc);
                    obj.TienThuNhapTruocThue = LuongTruocThue;
                    //end
                    //tiền thuế thu nhập
                    if (LuongTruocThue > 0)
                    {

                        var mucchiuthue = from mucchuithue in _DBTinhLuong.dm_MucChuiThues
                                          select new
                                          {
                                              mucchuithue.SoTienMucChiuThue
                                          };
                        var phantramchiuthue = from phatramchiu in _DBTinhLuong.dm_ThueThuNhapCaNhans
                                               select new
                                               {
                                                   phatramchiu.Tu,
                                                   phatramchiu.Den,
                                                   phatramchiu.Max,
                                                   phatramchiu.Min,
                                                   phatramchiu.PhanTramChiuThue
                                               };
                        decimal SoTienChiuThue = LuongTruocThue - (mucchiuthue.First().SoTienMucChiuThue != null ? mucchiuthue.First().SoTienMucChiuThue.Value : 0);
                        if (SoTienChiuThue > 0)
                        {
                            if (SoTienChiuThue < 5000000)
                                TienThueThuNhap = SoTienChiuThue * (decimal)0.05;
                            else if (SoTienChiuThue < 10000000)
                                TienThueThuNhap = SoTienChiuThue * (decimal)0.1 - (decimal)(0.05 * 5000000);
                            else if (SoTienChiuThue < 18000000)
                                TienThueThuNhap = SoTienChiuThue * (decimal)0.15 - (decimal)(0.1 * 5000000 + 0.05 * 5000000);
                            else if (SoTienChiuThue < 32000000)
                                TienThueThuNhap = SoTienChiuThue * (decimal)0.2 - (decimal)(0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                            else if (SoTienChiuThue < 52000000)
                                TienThueThuNhap = SoTienChiuThue * (decimal)0.25 - (decimal)(0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                            else if (SoTienChiuThue < 80000000)
                                TienThueThuNhap = SoTienChiuThue * (decimal)0.3 - (decimal)(0.25 * 20000000 + 0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                            else TienThueThuNhap = SoTienChiuThue * (decimal)0.35 - (decimal)(0.3 * 28000000 + 0.25 * 20000000 + 0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);

                        }
                        else TienThueThuNhap = 0;
                    }
                    else TienThueThuNhap = 0;
                      obj.TienThueThuNhap = TienThueThuNhap;
                    //end
                    //tiền thực lãnh
                      if (LuongTruocThue < 0)
                          tienthuclanh = LuongTruocThue + SoTienNguoiPhuThuoc;
                      else
                          tienthuclanh = LuongTruocThue - TienThueThuNhap;
                    obj.TienThucLanh = tienthuclanh;
                    //end
                    //tổng tiền nhập được
                    tongtiennhanduoc = tienthuclanh - TienTamUng -SoTienThuongChuyenCongNhat;
                    obj.TongTienNhanDuoc = tongtiennhanduoc;
                    //end
                    //trả gộp
                    obj.TienTraGop = (LuongCongNhat + PCTrachNhiemCongNhat + ChuyenCanCongNhat+ PhuCapDiLaiCongNhat+ PhuCapTienComCongNhat + PhuCapDienThoaiCongNhat + TienNghiLeNghiPhepCheDo + TienHuongNgungLamViec+ TroCapThoiViec + SoTienThuongKmCongNhat + SoTienThuongChuyenCongNhat + SoTienThuongHieuQuaCongViec + DieuChinh);
                    //end
                    //khấu trừ
                    obj.TongKhauTru = (SoTienThuongChuyenCongNhat+ TienDongBaoHiemYTe + TienDongBaoHiemXaHoi+ TienDongBaoHiemThatNghiep + TienThueThuNhap + TienTamUng);
                    //end
                    #endregion

                    _DBTinhLuong.SubmitChanges();
                   
                    #region lấy lại tr
                    string[] ArryStr = new string[2];
                    string bienchuoitam = "";
                    hs_NhanVien NhanVien = this.GetNhanVienById(obj.MaNhanVien);

                    #region tr cột nhân viên là lương
                    string stringtr = "<td><a onclick=\"javascript:UpdateItemTinhLuong(" + obj.Id + ")\"><img src=\"/Content/ImagesAdmin/Save.jpg\" style=\"width:15px;height:15px;\"></a></td>" +
                                            "<td nowrap=\"nowrap\">" + NhanVien.SoThe + "</td>" +
                                            "<td nowrap=\"nowrap\">" + NhanVien.Ho + "</td>" +
                                            "<td nowrap=\"nowrap\">" + NhanVien.Ten + "</td>";
                    bienchuoitam = (obj.SoTienNguoiPhuThuoc != null ? obj.SoTienNguoiPhuThuoc.Value.ToString("#,#0").Replace(",", ".") : "");
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + bienchuoitam + "\"><span id=\"SoTienNguoiPhuThuoc\" class=\"spanding\">" + bienchuoitam + "</span></td>";
                    bienchuoitam = (obj.LuongCanBan != null ? obj.LuongCanBan.Value.ToString("#,#0").Replace(",", ".") : "");
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;color:Red\">" + bienchuoitam + "</td>";
                    bienchuoitam = (obj.LuongCongNhat != null ? obj.LuongCongNhat.Value.ToString("#,#0").Replace(",", ".") : "");
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + bienchuoitam + "\"><span id=\"LuongCongNhat\" class=\"spanding\">" + bienchuoitam + "</span></td>";
                    bienchuoitam = (obj.PhucCapTrachNhiem != null ? obj.PhucCapTrachNhiem.Value.ToString("#,#0").Replace(",", ".") : "");
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;color:Red\">" + bienchuoitam + "</td>";
                    bienchuoitam = (obj.PCTrachNhiemCongNhat != null ? obj.PCTrachNhiemCongNhat.Value.ToString("#,#0").Replace(",", ".") : "");
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + bienchuoitam + "\"><span id=\"PCTrachNhiemCongNhat\" class=\"spanding\">" + bienchuoitam + "</span></td>";
                    bienchuoitam = (obj.ChuyenCan != null ? obj.ChuyenCan.Value.ToString("#,#").Replace(",", ".") : "");
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;color:Red\">" + bienchuoitam + "</td>";
                    bienchuoitam = (obj.ChuyenCanCongNhat != null ? obj.ChuyenCanCongNhat.Value.ToString("#,#0").Replace(",", ".") : "");
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + bienchuoitam + "\"><span id=\"ChuyenCanCongNhat\" class=\"spanding\">" + bienchuoitam + "</span></td>";
                    bienchuoitam = (obj.PhuCapDiLai != null ? obj.PhuCapDiLai.Value.ToString("#,#0").Replace(",", ".") : "");
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;color:Red\">" + bienchuoitam + "</td>";
                    bienchuoitam = obj.PhuCapDiLaiCongNhat != null ? obj.PhuCapDiLaiCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + bienchuoitam + "\"><span id=\"PhuCapDiLaiCongNhat\" class=\"spanding\">" + bienchuoitam + "</span></td>";
                    bienchuoitam = obj.PhuCapTienCom != null ? obj.PhuCapTienCom.Value.ToString("#,#0").Replace(",", ".") : "";
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;color:Red\">" + bienchuoitam + "</td>";
                    bienchuoitam = obj.PhuCapTienComCongNhat != null ? obj.PhuCapTienComCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + bienchuoitam + "\"><span id=\"PhuCapTienComCongNhat\" class=\"spanding\">" + bienchuoitam + "</span></td>";
                    bienchuoitam = obj.PhuCapDienThoai != null ? obj.PhuCapDienThoai.Value.ToString("#,#0").Replace(",", ".") : "";
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;color:Red\">" + bienchuoitam + "</td>";
                    bienchuoitam = obj.PhuCapDienThoaiCongNhat != null ? obj.PhuCapDienThoaiCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + bienchuoitam + "\"><span id=\"PhuCapDienThoaiCongNhat\" class=\"spanding\">" + bienchuoitam + "</span></td>";
                    bienchuoitam = obj.TienNghiLeNghiPhepCheDo != null ? obj.TienNghiLeNghiPhepCheDo.Value.ToString("#,#0").Replace(",", ".") : "";
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\">" + bienchuoitam + "</td>";
                    bienchuoitam = obj.SoTienThuongKmCongNhat != null ? obj.SoTienThuongKmCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + bienchuoitam + "\"><span id=\"SoTienThuongKmCongNhat\" class=\"spanding\">" + bienchuoitam + "</span></td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;color:Red\">" + (obj.SoTienThuongChuyenCongNhat != null ? obj.SoTienThuongChuyenCongNhat.Value.ToString("#,#0").Replace(",", ".") : "") + "</td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.SoTienThuongHieuQuaCongViec != null ? obj.SoTienThuongHieuQuaCongViec.Value.ToString("#,#0").Replace(",", ".") : "") + "\"><span id=\"SoTienThuongHieuQuaCongViec\" class=\"spanding\">" + (obj.SoTienThuongHieuQuaCongViec != null ? obj.SoTienThuongHieuQuaCongViec.Value.ToString("#,#0").Replace(",", ".") : "") + "</span></td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.DieuChinh != null ? obj.DieuChinh.Value.ToString("#,#0").Replace(",", ".") : "") + "\"><span id=\"DieuChinh\" class\"spanding\">" + (obj.DieuChinh != null ? obj.DieuChinh.Value.ToString("#,#0").Replace(",", ".") : "") + "</span></td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.TienDongBaoHiemXaHoi != null ? obj.TienDongBaoHiemXaHoi.Value.ToString("#,#0").Replace(",", ".") : "") + "\"><span id=\"TienDongBaoHiemXaHoi\" class=\"spanding\">" + (obj.TienDongBaoHiemXaHoi != null ? obj.TienDongBaoHiemXaHoi.Value.ToString("#,#0").Replace(",", ".") : "") + "</span></td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.TienDongBaoHiemYTe != null ? obj.TienDongBaoHiemYTe.Value.ToString("#,#0").Replace(",", ".") : "") + "\"><span id=\"TienDongBaoHiemYTe\" class=\"spanding\">" + (obj.TienDongBaoHiemYTe != null ? obj.TienDongBaoHiemYTe.Value.ToString("#,#0").Replace(",", ".") : "") + "</span></td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.TienThuNhapTruocThue != null ? obj.TienThuNhapTruocThue.Value.ToString("#,#0").Replace(",", ".") : "") + "\"><span id=\"TienThuNhapTruocThue\" class=\"spanding\">" + (obj.TienThuNhapTruocThue != null ? obj.TienThuNhapTruocThue.Value.ToString("#,#0").Replace(",", ".") : "") + "</span></td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.TienThueThuNhap != null ? obj.TienThueThuNhap.Value.ToString("#,#0").Replace(",", ".") : "") + "\"><span id=\"TienThueThuNhap\" class=\"spanding\">" + (obj.TienThueThuNhap != null ? obj.TienThueThuNhap.Value.ToString("#,#0").Replace(",", ".") : "") + "</span></td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\">" + (obj.TienThucLanh != null ? obj.TienThucLanh.Value.ToString("#,#0").Replace(",", ".") : "") + "</td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\">" + (obj.TienTamUng != null ? obj.TienTamUng.Value.ToString("#,#0").Replace(",", ".") : "") + "</td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\">" + (obj.TongTienNhanDuoc != null ? obj.TongTienNhanDuoc.Value.ToString("#,#0").Replace(",", ".") : "") + "</td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.TienTraGop != null ? obj.TienTraGop.Value.ToString("#,#0").Replace(",", ".") : "") + "\"><span id=\"TienTraGop\" class=\"spanding\">" + (obj.TienTraGop != null ? obj.TienTraGop.Value.ToString("#,#0").Replace(",", ".") : "") + "</span></td>";

                    stringtr += "<td nowrap=\"nowrap\" style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.TongKhauTru != null ? obj.TongKhauTru.Value.ToString("#,#0").Replace(",", ".") : "") + "\"><span id=\"TongKhauTru\" class=\"spanding\">" + (obj.TongKhauTru != null ? obj.TongKhauTru.Value.ToString("#,#0").Replace(",", ".") : "") + "</span></td>";
                    ArryStr[0] = stringtr;
                    #endregion

                    #region tr số ngày tính lương
                    string stringtr1 = " <td></td><td ></td><td ></td><td ></td><td ></td><td ></td>"+
                        "<td style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\""+(obj.NgayCongLamViec!=null?obj.NgayCongLamViec.Value.ToString("#,#0").Replace(".",","):"")+"\"><span id=\"NgayCongLamViec\" class=\"spanding\">"+(obj.NgayCongLamViec!=null?obj.NgayCongLamViec.Value.ToString("#,#0").Replace(".",","):"")+"</span></td>"+
                               "<td ></td>"+
                               "<td style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\""+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"\"><span id=\"NgayTinhPhuCap\" class=\"spanding\">"+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"</span></td>"+
                                "<td ></td>"+
                                "<td style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\""+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"\"><span id=\"NgayTinhPhuCap\" class=\"spanding\">"+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"</span></td>"+
                                "<td ></td>"+
                                "<td style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\""+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"\"><span id=\"NgayTinhPhuCap\" class=\"spanding\">"+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"</span></td>"+
                                "<td ></td>"+
                                "<td style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\""+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"\"><span id=\"NgayTinhPhuCap\" class=\"spanding\">"+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"</span></td>"+
                                "<td ></td>"+
                                "<td style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\""+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"\"><span id=\"NgayTinhPhuCap\" class=\"spanding\">"+(obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".",","):"")+"</span></td>"+
                                "<td ></td>"+
                                "<td style=\"text-align: center;\" ondblclick=\"javascritp:EditColumns(this);\" title=\"" + (obj.NghiLeNghiPhepCheDo != null ? obj.NghiLeNghiPhepCheDo.Value.ToString("#,#0").Replace(".", ",") : "") + "\"><span id=\"NghiLeNghiPhepCheDo\" class=\"spanding\">" + (obj.NghiLeNghiPhepCheDo != null ? obj.NghiLeNghiPhepCheDo.Value.ToString("#,#0").Replace(".", ",") : "") + "</span></td>" +
                                "<td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td><td ></td>";
                    ArryStr[1] = stringtr1;
                    #endregion
                    #endregion

                    return ArryStr;
                }
                return null; 
                
            }
            catch (Exception) {
                return null;  
            }
        }

        #endregion

        public bool XoaThangTinhLuong(int id, int thang, int nam)//id là IdBangTinhLuong
        {
            try 
            {
                tb_DanhSachBangTinhLuong danhsachbangtinhluong = this.GetDanhSachTinhLuongById(id);
                if (danhsachbangtinhluong.IsFinish || danhsachbangtinhluong.IsLock)
                    return false;
                using (var transaction = new System.Transactions.TransactionScope())
                {
                    (from tb_danhsachtinhluong in _DBTinhLuong.tb_DanhSachBangTinhLuongs
                     where tb_danhsachtinhluong.ID == id
                     select tb_danhsachtinhluong).ToList().ForEach((ct_thangtinhluong) =>
                     {
                         ct_thangtinhluong.IsDelete = true;
                     });
                    (from ct_BangTinhLuongChiTiet in _DBTinhLuong.tb_TinhLuongThangChiTiets
                     where ct_BangTinhLuongChiTiet.IdBangTinhLuong == id && ct_BangTinhLuongChiTiet.ThangTinhLuong == thang && ct_BangTinhLuongChiTiet.NamTinhLuong == nam
                     select ct_BangTinhLuongChiTiet).ToList().ForEach((ct_BangTinhLuongChiTiet) =>
                     {
                         ct_BangTinhLuongChiTiet.IsDelete = true;
                     });
                    _DBTinhLuong.SubmitChanges();
                    transaction.Complete();
                    return true;
                }
            }
            catch (Exception) { return false; }
            
        }

        public bool KhoaThangTinhLuong(int id, int thang, int nam)//id là IdBangTinhLuong
        {
            try
            {
                tb_DanhSachBangTinhLuong danhsachbangtinhluong = this.GetDanhSachTinhLuongById(id);
                if (danhsachbangtinhluong.IsFinish)
                    return false;
                using (var transaction = new System.Transactions.TransactionScope())
                {
                    (from tb_danhsachtinhluong in _DBTinhLuong.tb_DanhSachBangTinhLuongs
                     where tb_danhsachtinhluong.ID == id
                     select tb_danhsachtinhluong).ToList().ForEach((ct_thangtinhluong) =>
                     {
                         ct_thangtinhluong.IsLock = true;
                     });
                    (from ct_BangTinhLuongChiTiet in _DBTinhLuong.tb_TinhLuongThangChiTiets
                     where ct_BangTinhLuongChiTiet.IdBangTinhLuong == id && ct_BangTinhLuongChiTiet.ThangTinhLuong == thang && ct_BangTinhLuongChiTiet.NamTinhLuong == nam
                     select ct_BangTinhLuongChiTiet).ToList().ForEach((ct_BangTinhLuongChiTiet) =>
                     {
                         ct_BangTinhLuongChiTiet.IsLock = true;
                     });
                    _DBTinhLuong.SubmitChanges();
                    transaction.Complete();
                    return true;
                }
            }
            catch (Exception) { return false; }

        }
        public bool MoKhoaThangTinhLuong(int id, int thang, int nam)//id là IdBangTinhLuong
        {
            try
            {
                tb_DanhSachBangTinhLuong danhsachbangtinhluong = this.GetDanhSachTinhLuongById(id);
                if (danhsachbangtinhluong.IsFinish || danhsachbangtinhluong.IsLock)
                    return false;
                using (var transaction = new System.Transactions.TransactionScope())
                {
                    (from tb_danhsachtinhluong in _DBTinhLuong.tb_DanhSachBangTinhLuongs
                     where tb_danhsachtinhluong.ID == id
                     select tb_danhsachtinhluong).ToList().ForEach((ct_thangtinhluong) =>
                     {
                         ct_thangtinhluong.IsLock = false;
                     });
                    (from ct_BangTinhLuongChiTiet in _DBTinhLuong.tb_TinhLuongThangChiTiets
                     where ct_BangTinhLuongChiTiet.IdBangTinhLuong == id && ct_BangTinhLuongChiTiet.ThangTinhLuong == thang && ct_BangTinhLuongChiTiet.NamTinhLuong == nam
                     select ct_BangTinhLuongChiTiet).ToList().ForEach((ct_BangTinhLuongChiTiet) =>
                     {
                         ct_BangTinhLuongChiTiet.IsLock = false;
                     });
                    _DBTinhLuong.SubmitChanges();
                    transaction.Complete();
                    return true;
                }
            }
            catch (Exception) { return false; }

        }
        public bool HoanThanhThangTinhLuong(int id, int thang, int nam)//id là IdBangTinhLuong
        {
            try
            {
                using (var transaction = new System.Transactions.TransactionScope())
                {
                    (from tb_danhsachtinhluong in _DBTinhLuong.tb_DanhSachBangTinhLuongs
                     where tb_danhsachtinhluong.ID == id
                     select tb_danhsachtinhluong).ToList().ForEach((ct_thangtinhluong) =>
                     {
                         ct_thangtinhluong.IsLock = true;
                         ct_thangtinhluong.IsFinish = true;
                     });
                    (from ct_BangTinhLuongChiTiet in _DBTinhLuong.tb_TinhLuongThangChiTiets
                     where ct_BangTinhLuongChiTiet.IdBangTinhLuong == id && ct_BangTinhLuongChiTiet.ThangTinhLuong == thang && ct_BangTinhLuongChiTiet.NamTinhLuong == nam
                     select ct_BangTinhLuongChiTiet).ToList().ForEach((ct_BangTinhLuongChiTiet) =>
                     {
                         ct_BangTinhLuongChiTiet.IsLock = true;
                         ct_BangTinhLuongChiTiet.IsFinish = true;
                     });
                    _DBTinhLuong.SubmitChanges();
                    transaction.Complete();
                    return true;
                }
            }
            catch (Exception) { return false; }

        }
        #endregion

        #region Build
        public TinhLuongHangThangForm BuildTinhLuongHangThang(int? id, int thang, int nam,bool dulieutonghop)//id bang danh sach tinh luong
        {
            TinhLuongHangThangForm form = new TinhLuongHangThangForm();
            form.TinhLuongHangThangDto = new List<TinhLuongDto>();
             IList<tb_TinhLuongThangChiTiet> ListTinhLuong;
            if(id!=null)
                ListTinhLuong = _DBTinhLuong.tb_TinhLuongThangChiTiets.Where(p => p.IdBangTinhLuong == id && (p.IsDelete==null||p.IsDelete==false)).ToList();
            else
                ListTinhLuong = _DBTinhLuong.tb_TinhLuongThangChiTiets.Where(p => p.ThangTinhLuong == thang && p.NamTinhLuong == nam && (p.IsDelete == null || p.IsDelete == false)).ToList();
            if (ListTinhLuong.Count > 0)
            {
                #region khai báo biến
                decimal tongngaylamviec, luongcanban, tienmotngaycong, LuongCongNhat, songayhuongcacphucap, PCTrachNhiemCongNhat, tienchuyencan, ChuyenCanCongNhat, PhuCapDiLaiCongNhat;
                decimal phucaptiencom, PhuCapTienComCongNhat, phucapdilai, phucaptrachnhiem, phucapdienthoai, PhuCapDienThoaiCongNhat;
                decimal TienNghiLeNghiPhepCheDo, SoTienThuongKmCongNhat, SoTienThuongChuyenCongNhat, SoTienThuongHieuQuaCongViec, DieuChinh, TienDongBaoHiemYTe, TienDongBaoHiemXaHoi, TienDongBaoHiemThatNghiep;
                decimal TienTamUng, TienHuongNgungLamViec, TroCapThoiViec, TienPhiCongDoan, LuongTruocThue, TienThueThuNhap, tienthuclanh, tongtiennhanduoc, SoTienNguoiPhuThuoc;

                #endregion
                form.IdBangChamCong = ListTinhLuong[0].IdBangChamCong;
                form.ThangTinhLuong = ListTinhLuong[0].ThangTinhLuong!=null?ListTinhLuong[0].ThangTinhLuong.Value.ToString():"";
                form.NamTinhLuong = ListTinhLuong[0].NamTinhLuong!=null?ListTinhLuong[0].NamTinhLuong.Value.ToString():"";
                form.IdBangTinhLuong = ListTinhLuong[0].IdBangTinhLuong;
                form.IsFinish = ListTinhLuong[0].IsFinish != null ? ListTinhLuong[0].IsFinish.Value : false;
                form.IsLock = ListTinhLuong[0].IsLock != null ? ListTinhLuong[0].IsLock.Value : false;
                form.IsDelete = ListTinhLuong[0].IsDelete;
                if (!dulieutonghop)
                {
                    foreach (var item in ListTinhLuong)
                    {
                        #region lấy dữ liệu từ bản chitiet tinh lương
                        TinhLuongDto _TinhLuongDto = new TinhLuongDto();
                        hs_NhanVien NhanVien = this.GetNhanVienById(item.MaNhanVien);
                        _TinhLuongDto.SoThe = NhanVien.SoThe;
                        _TinhLuongDto.Ho = NhanVien.Ho;
                        _TinhLuongDto.Ten = NhanVien.Ten;
                        _TinhLuongDto.Id = item.Id;
                        _TinhLuongDto.IdBangChamCong = item.IdBangChamCong;
                        _TinhLuongDto.MaNhanVien = item.MaNhanVien;
                        _TinhLuongDto.NgayCongLamViec = item.NgayCongLamViec != null ? item.NgayCongLamViec.Value.ToString("#,#0").Replace(".", ",") : "";
                        _TinhLuongDto.NgayTinhPhuCap = item.NgayTinhPhuCap != null ? item.NgayTinhPhuCap.Value.ToString("#,#0").Replace(".", ",") : "";
                        _TinhLuongDto.SoTienNguoiPhuThuoc = item.SoTienNguoiPhuThuoc != null ? item.SoTienNguoiPhuThuoc.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.LuongCanBan = item.LuongCanBan != null ? item.LuongCanBan.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.LuongCongNhat = item.LuongCongNhat != null ? item.LuongCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.PhuCapTrachNhiem = item.PhucCapTrachNhiem != null ? item.PhucCapTrachNhiem.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.PCTrachNhiemCongNhat = item.PCTrachNhiemCongNhat != null ? item.PCTrachNhiemCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.ChuyenCan = item.ChuyenCan != null ? item.ChuyenCan.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.ChuyenCanCongNhat = item.ChuyenCanCongNhat != null ? item.ChuyenCanCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.PhuCapDiLai = item.PhuCapDiLai != null ? item.PhuCapDiLai.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.PhuCapDiLaiCongNhat = item.PhuCapDiLaiCongNhat != null ? item.PhuCapDiLaiCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.PhuCapTienCom = item.PhuCapTienCom != null ? item.PhuCapTienCom.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.PhuCapTienComCongNhat = item.PhuCapTienComCongNhat != null ? item.PhuCapTienComCongNhat.Value.ToString("#,#0  ").Replace(",", ".") : "";
                        _TinhLuongDto.PhuCapDienThoai = item.PhuCapDienThoai != null ? item.PhuCapDienThoai.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.PhuCapDienThoaiCongNhat = item.PhuCapDienThoaiCongNhat != null ? item.PhuCapDienThoaiCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.NghiLeNghiPhepCheDo = item.NghiLeNghiPhepCheDo != null ? item.NghiLeNghiPhepCheDo.Value : 0;
                        _TinhLuongDto.TienNghiLeNghiPhepCheDo = item.TienNghiLeNghiPhepCheDo != null ? item.TienNghiLeNghiPhepCheDo.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.IdThuongKm = item.IdThuongKm != null ? item.IdThuongKm.Value : 0;
                        _TinhLuongDto.SoTienThuongKmCongNhat = item.SoTienThuongKmCongNhat != null ? item.SoTienThuongKmCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.IdThuongChuyen = item.IdThuongChuyen != null ? item.IdThuongChuyen.Value : 0;
                        _TinhLuongDto.SoTienThuongChuyenCongNhat = item.SoTienThuongChuyenCongNhat != null ? item.SoTienThuongChuyenCongNhat.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.SoTienThuongHieuQuaCongViec = item.SoTienThuongHieuQuaCongViec != null ? item.SoTienThuongHieuQuaCongViec.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.DieuChinh = item.DieuChinh != null ? item.DieuChinh.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.IdCauHinhBaoHiem = item.IdCauHinhBaoHiem != null ? item.IdCauHinhBaoHiem.Value : 0;
                        _TinhLuongDto.TienDongBaoHiemYTe = item.TienDongBaoHiemYTe != null ? item.TienDongBaoHiemYTe.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TienDongBaoHiemXaHoi = item.TienDongBaoHiemXaHoi != null ? item.TienDongBaoHiemXaHoi.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TienDongBaoHiemThatNghiep = item.TienDongBaoHiemThatNghiep != null ? item.TienDongBaoHiemThatNghiep.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TienThuNhapTruocThue = item.TienThuNhapTruocThue != null ? item.TienThuNhapTruocThue.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TienThueThuNhap = item.TienThueThuNhap != null ? item.TienThueThuNhap.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TienThucLanh = item.TienThucLanh != null ? item.TienThucLanh.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TienTamUng = item.TienTamUng != null ? item.TienTamUng.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TongTienNhanDuoc = item.TongTienNhanDuoc != null ? item.TongTienNhanDuoc.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TienTraGop = item.TienTraGop != null ? item.TienTraGop.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.TongKhauTru = item.TongKhauTru != null ? item.TongKhauTru.Value.ToString("#,#0").Replace(",", ".") : "";
                        _TinhLuongDto.ThangTinhLuong = item.ThangTinhLuong ?? 0;
                        _TinhLuongDto.NamTinhLuong = item.NamTinhLuong ?? 0;
                        _TinhLuongDto.TuNgayTinhLuong = item.TuNgayTinhLuong != null ? item.TuNgayTinhLuong.Value.ToString("dd/MM/yyyy") : "";
                        _TinhLuongDto.DenNgayTinhLuong = item.DenNgayTinhLuong != null ? item.DenNgayTinhLuong.Value.ToString("dd/MM/yyyy") : "";
                        _TinhLuongDto.IsFinish = item.IsFinish != null ? item.IsFinish.Value : false;
                        _TinhLuongDto.IsLock = item.IsLock != null ? item.IsLock.Value : false;
                        _TinhLuongDto.IsDelete = item.IsDelete;
                        #endregion
                        form.TinhLuongHangThangDto.Add(_TinhLuongDto);
                    }
                }
                else
                {
                    foreach (var item in ListTinhLuong)
                    {
                        #region lấy dữ liệu tổng hợp
                        TinhLuongDto _TinhLuongDto = new TinhLuongDto();
                        hs_NhanVien NhanVien = this.GetNhanVienById(item.MaNhanVien);
                        _TinhLuongDto.SoThe = NhanVien.SoThe;
                        _TinhLuongDto.Ho = NhanVien.Ho;
                        _TinhLuongDto.Ten = NhanVien.Ten;
                        _TinhLuongDto.Id = item.Id;
                        _TinhLuongDto.IdBangChamCong = item.IdBangChamCong;
                        _TinhLuongDto.MaNhanVien = item.MaNhanVien;
                        var query = from bangchamcong in _DBTinhLuong.CC_BangChamCongs
                                    join
                                        chamcongngay in _DBTinhLuong.CC_ChamCongNgayNhanViens on bangchamcong.ID equals chamcongngay.MaBangChamCong
                                    where bangchamcong.Thang == item.ThangTinhLuong && bangchamcong.Nam == item.NamTinhLuong && chamcongngay.MaNhanVien == item.MaNhanVien
                                    orderby chamcongngay.NgayTao descending
                                    select new
                                    {
                                        bangchamcong,
                                        chamcongngay
                                    };
                        _TinhLuongDto.IdBangChamCong = query.First().bangchamcong.ID;
                        _TinhLuongDto.MaNhanVien = query.First().chamcongngay.MaNhanVien;
                        _TinhLuongDto.IdBangTinhLuong = item.IdBangTinhLuong;
                        // tiền người phụ thuộc
                        SoTienNguoiPhuThuoc = item.SoTienNguoiPhuThuoc != null ? item.SoTienNguoiPhuThuoc.Value : 0;
                        _TinhLuongDto.SoTienNguoiPhuThuoc = SoTienNguoiPhuThuoc.ToString("#,#0").Replace(",",".");

                        //luong ban bản
                        tongngaylamviec = query.First().chamcongngay.TongSoNgayLamViec;
                        luongcanban = this.GetLuongCanBanCuaNhanVien(query.First().chamcongngay.MaNhanVien);
                        tienmotngaycong = luongcanban / 26;
                        _TinhLuongDto.NgayCongLamViec = tongngaylamviec.ToString("#,#0").Replace(".", ",");
                        _TinhLuongDto.LuongCanBan = luongcanban.ToString("#,#0").Replace(",", ".");
                        LuongCongNhat = (tienmotngaycong * tongngaylamviec);
                        _TinhLuongDto.LuongCongNhat = LuongCongNhat.ToString("#,#0").Replace(",", ".");//tinhLuongChiTiet.LuongCanBan;

                        songayhuongcacphucap = query.First().chamcongngay.TongSoNgayLamViec + query.First().chamcongngay.TongNghiLe + query.First().chamcongngay.TongNghiPhepNam + query.First().chamcongngay.TongNghiCheDo;
                        _TinhLuongDto.NgayTinhPhuCap = songayhuongcacphucap.ToString("#,#0").Replace(",", ".");
                        //trach nhiem
                        phucaptrachnhiem = this.GetTienPhuCapTrachNhiem(query.First().chamcongngay.MaNhanVien);
                        _TinhLuongDto.PhuCapTrachNhiem = phucaptrachnhiem.ToString("#,#0").Replace(",", ".");
                        PCTrachNhiemCongNhat = ((phucaptrachnhiem / 26) * songayhuongcacphucap);
                        _TinhLuongDto.PCTrachNhiemCongNhat = PCTrachNhiemCongNhat.ToString("#,#0").Replace(",", ".");//tinhLuongChiTiet.PhucCapTrachNhiem;
                        //chuyen cần
                        tienchuyencan = this.GetTienChuyenCan(query.First().chamcongngay.MaNhanVien);
                        _TinhLuongDto.ChuyenCan = tienchuyencan.ToString("#,#0").Replace(",", ".");
                        ChuyenCanCongNhat = ((tienchuyencan / 26) * songayhuongcacphucap);
                        _TinhLuongDto.ChuyenCanCongNhat = ChuyenCanCongNhat.ToString("#,#0").Replace(",", ".");// tinhLuongChiTiet.ChuyenCan;
                        // đi lại
                        phucapdilai = this.GetPhuCapDiLai(query.First().chamcongngay.MaNhanVien);
                        _TinhLuongDto.PhuCapDiLai = phucapdilai.ToString("#,#0").Replace(",", ".");
                        PhuCapDiLaiCongNhat = ((phucapdilai / 26) * songayhuongcacphucap);
                        _TinhLuongDto.PhuCapDiLaiCongNhat = PhuCapDiLaiCongNhat.ToString("#,#0").Replace(",", "."); //tinhLuongChiTiet.PhuCapDiLai;
                        //tiền cơm
                        phucaptiencom = this.GetPhuCapTienCom(query.First().chamcongngay.MaNhanVien);
                        _TinhLuongDto.PhuCapTienCom = phucaptiencom.ToString("#,#0").Replace(",", ".");
                        PhuCapTienComCongNhat = ((phucaptiencom / 26) * songayhuongcacphucap);
                        _TinhLuongDto.PhuCapTienComCongNhat = PhuCapTienComCongNhat.ToString("#,#0").Replace(",", ".");//tinhLuongChiTiet.PhuCapTienCom;
                        //tiền điện thoại
                        phucapdienthoai = this.GetPhuCapDienThoai(query.First().chamcongngay.MaNhanVien);
                        _TinhLuongDto.PhuCapDienThoai = phucapdienthoai.ToString("#,#0").Replace(",", ".");
                        PhuCapDienThoaiCongNhat = ((phucapdienthoai / 26) * songayhuongcacphucap);
                        _TinhLuongDto.PhuCapDienThoaiCongNhat = PhuCapDienThoaiCongNhat.ToString("#,#0").Replace(",", ".");//tinhLuongChiTiet.PhuCapDienThoai;
                        // nghỉ phép chế độ
                        int songaynghiphepnamchedoi = Convert.ToInt32(query.First().chamcongngay.TongNghiLe + query.First().chamcongngay.TongNghiCheDo + query.First().chamcongngay.TongNghiPhepNam);
                        _TinhLuongDto.NghiLeNghiPhepCheDo = songaynghiphepnamchedoi;
                        TienNghiLeNghiPhepCheDo = this.GetTienNghiLe(songaynghiphepnamchedoi, tienmotngaycong);
                        _TinhLuongDto.TienNghiLeNghiPhepCheDo = TienNghiLeNghiPhepCheDo.ToString("#,#0").Replace(",", ".");

                        _TinhLuongDto.IdThuongKm = 0;
                        SoTienThuongKmCongNhat = 0;
                        _TinhLuongDto.SoTienThuongKmCongNhat = SoTienThuongKmCongNhat.ToString("#,#0").Replace(",", ".");
                        SoTienThuongChuyenCongNhat = this.GetTienThuongChuyen(query.First().chamcongngay.MaNhanVien);
                        _TinhLuongDto.SoTienThuongChuyenCongNhat = SoTienThuongChuyenCongNhat.ToString("#,#0").Replace(",", ".");

                        SoTienThuongHieuQuaCongViec = 0;
                        _TinhLuongDto.SoTienThuongHieuQuaCongViec = SoTienThuongHieuQuaCongViec.ToString("#,#0").Replace(",", ".");//nhập
                        DieuChinh = 0;
                        _TinhLuongDto.DieuChinh = DieuChinh.ToString("#,#0").Replace(",", ".");

                        TienDongBaoHiemYTe = 0;
                        _TinhLuongDto.TienDongBaoHiemYTe = TienDongBaoHiemYTe.ToString("#,#0").Replace(",", ".");

                        TienDongBaoHiemXaHoi = 0;
                        _TinhLuongDto.TienDongBaoHiemXaHoi = TienDongBaoHiemXaHoi.ToString("#,#0").Replace(",", ".");
                        TienDongBaoHiemThatNghiep = 0;
                        _TinhLuongDto.TienDongBaoHiemThatNghiep = TienDongBaoHiemThatNghiep.ToString("#,#0").Replace(",", ".");
                        TienTamUng = 0;
                        _TinhLuongDto.TienTamUng = TienTamUng.ToString("#,#0").Replace(",", ".");
                        // thôi việc
                        TienHuongNgungLamViec = 0;
                        TroCapThoiViec = 0;
                        TienPhiCongDoan = 0;
                        //thu nhập trước thuế
                        LuongTruocThue = (LuongCongNhat + PCTrachNhiemCongNhat + ChuyenCanCongNhat + PhuCapDiLaiCongNhat + PhuCapTienComCongNhat + PhuCapDienThoaiCongNhat + TienNghiLeNghiPhepCheDo + TienHuongNgungLamViec + TroCapThoiViec + SoTienThuongKmCongNhat + SoTienThuongChuyenCongNhat + SoTienThuongHieuQuaCongViec + DieuChinh) - (TienDongBaoHiemYTe + TienDongBaoHiemXaHoi + TienDongBaoHiemThatNghiep + TienPhiCongDoan + SoTienNguoiPhuThuoc);
                        _TinhLuongDto.TienThuNhapTruocThue = LuongTruocThue.ToString("#,#0").Replace(",", ".");
                        //end
                        //tiền thuế thu nhập
                        TienThueThuNhap = LuongTruocThue * (decimal)0.05;
                        _TinhLuongDto.TienThueThuNhap = TienThueThuNhap.ToString("#,#0").Replace(",", ".");
                        //end
                        //tiền thực lãnh
                        tienthuclanh = LuongTruocThue - TienThueThuNhap;
                        _TinhLuongDto.TienThucLanh = tienthuclanh.ToString("#,#0").Replace(",", ".");
                        //end
                        //tổng tiền nhập được
                        tongtiennhanduoc = tienthuclanh - TienTamUng;
                        _TinhLuongDto.TongTienNhanDuoc = tongtiennhanduoc.ToString("#,#0").Replace(",", ".");
                        //end
                        _TinhLuongDto.ThangTinhLuong = item.ThangTinhLuong != null ? item.ThangTinhLuong.Value : 0;
                        _TinhLuongDto.NamTinhLuong = item.NamTinhLuong != null ? item.NamTinhLuong.Value : 0;
                        _TinhLuongDto.IsFinish = item.IsFinish != null ? item.IsFinish.Value : false;
                        _TinhLuongDto.IsLock = item.IsLock != null ? item.IsLock.Value : false;
                        _TinhLuongDto.IsDelete = item.IsDelete;
                        #endregion
                        form.TinhLuongHangThangDto.Add(_TinhLuongDto);
                    }
                }
            }
            return form;
        }

        public tb_DanhSachBangTinhLuong BuildDanhSachTinhLuong(LapBangTinhLuongForm form)
        {
            tb_DanhSachBangTinhLuong obj = new tb_DanhSachBangTinhLuong();
            obj.Thang = form.Thang;
            obj.Nam = form.Nam;
            obj.HoTenNguoiLapBieu = form.HoTenNguoiLapBieu;
            obj.GhiChu = form.GhiChu;
            obj.IsLock = false;
            obj.IsFinish = false;
            obj.NguoiTao = Convert.ToInt32(HttpContext.Current.Session["USERID"]);
            obj.NgayTao = DateTime.Now;
            obj.NguoiSua = Convert.ToInt32(HttpContext.Current.Session["USERID"]);
            obj.NgaySua = DateTime.Now;
            return obj;
        }

        public LapBangTinhLuongForm BuildLapBangTinhLuong(int id)
        {
            LapBangTinhLuongForm form = new LapBangTinhLuongForm();
            tb_DanhSachBangTinhLuong obj = this.GetDanhSachTinhLuongById(id);
            form.Thang = obj.Thang;
            form.Nam = obj.Nam;
            form.HoTenNguoiLapBieu = obj.HoTenNguoiLapBieu;
            form.GhiChu = obj.GhiChu;
            form.HoTenNguoiKy = obj.HoTenNguoiKy;
            form.ChucVuNguoiKy = obj.ChucVuNguoiKy;
            form.IsFinish = obj.IsFinish;
            form.IsLock = obj.IsLock;
            form.NgayKy = obj.NgayKy != null ? obj.NgayKy.Value.ToString("dd/MM/yyyy") : "";
            return form;
        }
        #endregion

        

        #region TAM.NN

        #region tổng hợp lại dữ liệu khi click nút tổng hợp dữ liệu(dữ liệu cũ sẽ được thay bằng toàn bộ dữ liệu mới.)
        public bool UpdateAllTinhLuongChitietTrongThang(int thang,int nam)
        {
            try
            {
                var bangtinhluongchitiet = from tinhluongchitiet in _DBTinhLuong.tb_TinhLuongThangChiTiets
                                           where tinhluongchitiet.ThangTinhLuong == thang && tinhluongchitiet.NamTinhLuong == nam
                                           select new
                                           {
                                               tinhluongchitiet
                                           };
                var mucchiuthue = from mucchuithue in _DBTinhLuong.dm_MucChuiThues
                                  select new
                                  {
                                      mucchuithue.SoTienMucChiuThue
                                  };
                var phantramchiuthue = from phatramchiu in _DBTinhLuong.dm_ThueThuNhapCaNhans
                                       select new
                                       {
                                           phatramchiu.Tu,
                                           phatramchiu.Den,
                                           phatramchiu.Max,
                                           phatramchiu.Min,
                                           phatramchiu.PhanTramChiuThue
                                       };
                dm_BaoHiem BaoHiem = this.BaoHiem();
                decimal PhanTramBHYT = BaoHiem != null ? BaoHiem.BHYT.Value : 0;
                decimal PhanTramBHXH = BaoHiem != null ? BaoHiem.BHXH.Value : 0;
                decimal PhanTramBHTN = BaoHiem != null ? BaoHiem.BHTN.Value : 0;
                using (var transaction = new System.Transactions.TransactionScope())
                {
                    #region khai báo biến
                    decimal tongngaylamviec, luongcanban, tienmotngaycong, songayhuongcacphucap, tienchuyencan;
                    decimal phucaptiencom, phucapdilai, phucaptrachnhiem, phucapdienthoai, LuongTruocThue, TienThueThuNhap, tienthuclanh, tongtiennhanduoc, SoTienNguoiPhuThuoc;
                    //decimal TienNghiLeNghiPhepCheDo, SoTienThuongKmCongNhat, SoTienThuongChuyenCongNhat, SoTienThuongHieuQuaCongViec, DieuChinh, TienDongBaoHiemYTe, TienDongBaoHiemXaHoi, TienDongBaoHiemThatNghiep;
                    //decimal LuongCongNhat, PCTrachNhiemCongNhat, PhuCapTienComCongNhat, PhuCapDienThoaiCongNhat, PhuCapDiLaiCongNhat, ChuyenCanCongNhat, TienTamUng, TienHuongNgungLamViec, TroCapThoiViec, TienPhiCongDoan;
                    #endregion

                    foreach (var item in bangtinhluongchitiet)
                    {
                        try
                        {
                            tb_TinhLuongThangChiTiet UpdatetinhLuongChiTiet = this.GetTinhLuongThangChiTiet(item.tinhluongchitiet.Id);
                            CC_ChamCongNgayNhanVien chamCongNgayNhanVien = this.GetBangChamCongById(item.tinhluongchitiet.MaNhanVien, thang, nam);
                            // người phụ thuộc
                            SoTienNguoiPhuThuoc = 0;// nhập
                            UpdatetinhLuongChiTiet.SoTienNguoiPhuThuoc = 0;//nhập
                            //luong ban bản
                            tongngaylamviec = chamCongNgayNhanVien.TongSoNgayLamViec;
                            luongcanban = this.GetLuongCanBanCuaNhanVien(chamCongNgayNhanVien.MaNhanVien);
                            tienmotngaycong = luongcanban / 26;
                            UpdatetinhLuongChiTiet.NgayCongLamViec = tongngaylamviec;
                            UpdatetinhLuongChiTiet.LuongCanBan = luongcanban;
                            UpdatetinhLuongChiTiet.LuongCongNhat = tienmotngaycong * tongngaylamviec;//tinhLuongChiTiet.LuongCanBan;
                            songayhuongcacphucap = chamCongNgayNhanVien.TongSoNgayLamViec + chamCongNgayNhanVien.TongNghiLe + chamCongNgayNhanVien.TongNghiPhepNam + chamCongNgayNhanVien.TongNghiCheDo;
                            UpdatetinhLuongChiTiet.NgayTinhPhuCap = songayhuongcacphucap;
                            //trach nhiem
                            phucaptrachnhiem = this.GetTienPhuCapTrachNhiem(chamCongNgayNhanVien.MaNhanVien);
                            UpdatetinhLuongChiTiet.PhucCapTrachNhiem = phucaptrachnhiem;
                            UpdatetinhLuongChiTiet.PCTrachNhiemCongNhat = (phucaptrachnhiem / 26) * songayhuongcacphucap;//tinhLuongChiTiet.PhucCapTrachNhiem;
                            //chuyen cần
                            tienchuyencan = this.GetTienChuyenCan(chamCongNgayNhanVien.MaNhanVien);
                            UpdatetinhLuongChiTiet.ChuyenCan = tienchuyencan;
                            UpdatetinhLuongChiTiet.ChuyenCanCongNhat = (tienchuyencan / 26) * songayhuongcacphucap;// tinhLuongChiTiet.ChuyenCan;
                            // đi lại
                            phucapdilai = this.GetPhuCapDiLai(chamCongNgayNhanVien.MaNhanVien);
                            UpdatetinhLuongChiTiet.PhuCapDiLai = phucapdilai;
                            UpdatetinhLuongChiTiet.PhuCapDiLaiCongNhat = (phucapdilai / 26) * songayhuongcacphucap; //tinhLuongChiTiet.PhuCapDiLai;
                            //tiền cơm
                            phucaptiencom = this.GetPhuCapTienCom(chamCongNgayNhanVien.MaNhanVien);
                            UpdatetinhLuongChiTiet.PhuCapTienCom = phucaptiencom;
                            UpdatetinhLuongChiTiet.PhuCapTienComCongNhat = (phucaptiencom / 26) * songayhuongcacphucap;//tinhLuongChiTiet.PhuCapTienCom;
                            //tiền điện thoại
                            phucapdienthoai = this.GetPhuCapDienThoai(chamCongNgayNhanVien.MaNhanVien);
                            UpdatetinhLuongChiTiet.PhuCapDienThoai = phucapdienthoai;
                            UpdatetinhLuongChiTiet.PhuCapDienThoaiCongNhat = (phucapdienthoai / 26) * songayhuongcacphucap;//tinhLuongChiTiet.PhuCapDienThoai;
                            // nghỉ phép chế độ
                            int songaynghiphepnamchedoi = Convert.ToInt32(chamCongNgayNhanVien.TongNghiLe + chamCongNgayNhanVien.TongNghiCheDo + chamCongNgayNhanVien.TongNghiPhepNam);
                            UpdatetinhLuongChiTiet.NghiLeNghiPhepCheDo = songaynghiphepnamchedoi;
                            UpdatetinhLuongChiTiet.TienNghiLeNghiPhepCheDo = this.GetTienNghiLe(songaynghiphepnamchedoi, tienmotngaycong);

                            UpdatetinhLuongChiTiet.IdThuongKm = 0;
                            UpdatetinhLuongChiTiet.SoTienThuongKmCongNhat = 0;//chưa biết sao
                            UpdatetinhLuongChiTiet.SoTienThuongChuyenCongNhat = this.GetTienThuongChuyen(chamCongNgayNhanVien.MaNhanVien);

                            UpdatetinhLuongChiTiet.SoTienThuongHieuQuaCongViec = 0;//nhập
                            UpdatetinhLuongChiTiet.DieuChinh = 0;//nhập

                            UpdatetinhLuongChiTiet.TienDongBaoHiemYTe = luongcanban * PhanTramBHYT;
                            UpdatetinhLuongChiTiet.TienDongBaoHiemXaHoi = luongcanban * PhanTramBHXH;
                            UpdatetinhLuongChiTiet.TienDongBaoHiemThatNghiep = luongcanban * PhanTramBHTN;
                            UpdatetinhLuongChiTiet.TienTamUng = this.TongTienTamUngCuaNhanVien(UpdatetinhLuongChiTiet.MaNhanVien, thang, nam);

                            // thôi việc
                            UpdatetinhLuongChiTiet.TienHuongNgungLamViec = 0;//chưa xác định
                            UpdatetinhLuongChiTiet.TroCapThoiViec = 0;//nhập
                            UpdatetinhLuongChiTiet.TienPhiCongDoan = 0;//nhập

                            //thu nhập trước thuế
                            LuongTruocThue = (UpdatetinhLuongChiTiet.LuongCongNhat.Value + UpdatetinhLuongChiTiet.PCTrachNhiemCongNhat.Value + UpdatetinhLuongChiTiet.ChuyenCanCongNhat.Value + UpdatetinhLuongChiTiet.PhuCapDiLaiCongNhat.Value + UpdatetinhLuongChiTiet.PhuCapTienComCongNhat.Value + UpdatetinhLuongChiTiet.PhuCapDienThoaiCongNhat.Value + UpdatetinhLuongChiTiet.TienNghiLeNghiPhepCheDo.Value + UpdatetinhLuongChiTiet.TienHuongNgungLamViec.Value + UpdatetinhLuongChiTiet.TroCapThoiViec.Value + UpdatetinhLuongChiTiet.SoTienThuongKmCongNhat.Value + UpdatetinhLuongChiTiet.SoTienThuongChuyenCongNhat.Value + UpdatetinhLuongChiTiet.SoTienThuongHieuQuaCongViec.Value + UpdatetinhLuongChiTiet.DieuChinh.Value) - (UpdatetinhLuongChiTiet.TienDongBaoHiemYTe.Value + UpdatetinhLuongChiTiet.TienDongBaoHiemXaHoi.Value + UpdatetinhLuongChiTiet.TienDongBaoHiemThatNghiep.Value + UpdatetinhLuongChiTiet.TienPhiCongDoan.Value + UpdatetinhLuongChiTiet.SoTienNguoiPhuThuoc.Value);
                            UpdatetinhLuongChiTiet.TienThuNhapTruocThue = LuongTruocThue;
                            //end
                            //tiền thuế thu nhập
                            if (LuongTruocThue > 0)
                            {
                                decimal SoTienChiuThue = LuongTruocThue - (mucchiuthue.First().SoTienMucChiuThue != null ? mucchiuthue.First().SoTienMucChiuThue.Value : 0);
                                if (SoTienChiuThue > 0)
                                {
                                    if (SoTienChiuThue < 5000000)
                                        TienThueThuNhap = SoTienChiuThue * (decimal)0.05;
                                    else if (SoTienChiuThue < 10000000)
                                        TienThueThuNhap = SoTienChiuThue * (decimal)0.1 - (decimal)(0.05 * 5000000);
                                    else if (SoTienChiuThue < 18000000)
                                        TienThueThuNhap = SoTienChiuThue * (decimal)0.15 - (decimal)(0.1 * 5000000 + 0.05 * 5000000);
                                    else if (SoTienChiuThue < 32000000)
                                        TienThueThuNhap = SoTienChiuThue * (decimal)0.2 - (decimal)(0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                                    else if (SoTienChiuThue < 52000000)
                                        TienThueThuNhap = SoTienChiuThue * (decimal)0.25 - (decimal)(0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                                    else if (SoTienChiuThue < 80000000)
                                        TienThueThuNhap = SoTienChiuThue * (decimal)0.3 - (decimal)(0.25 * 20000000 + 0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                                    else TienThueThuNhap = SoTienChiuThue * (decimal)0.35 - (decimal)(0.3 * 28000000 + 0.25 * 20000000 + 0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);

                                }
                                else TienThueThuNhap = 0;
                            }
                            else TienThueThuNhap = 0;
                            UpdatetinhLuongChiTiet.TienThueThuNhap = TienThueThuNhap;
                            //end
                            //tiền thực lãnh
                            if (LuongTruocThue < 0)
                                tienthuclanh = LuongTruocThue + SoTienNguoiPhuThuoc;
                            else
                                tienthuclanh = LuongTruocThue - TienThueThuNhap;
                            UpdatetinhLuongChiTiet.TienThucLanh = tienthuclanh;
                            //end
                            //tổng tiền nhập được
                            tongtiennhanduoc = tienthuclanh - UpdatetinhLuongChiTiet.TienTamUng.Value - UpdatetinhLuongChiTiet.SoTienThuongChuyenCongNhat.Value;
                            UpdatetinhLuongChiTiet.TongTienNhanDuoc = tongtiennhanduoc;
                            //end
                            //trả gộp
                            UpdatetinhLuongChiTiet.TienTraGop = (UpdatetinhLuongChiTiet.LuongCongNhat.Value + UpdatetinhLuongChiTiet.PCTrachNhiemCongNhat.Value + UpdatetinhLuongChiTiet.ChuyenCanCongNhat.Value + UpdatetinhLuongChiTiet.PhuCapDiLaiCongNhat.Value + UpdatetinhLuongChiTiet.PhuCapTienComCongNhat.Value + UpdatetinhLuongChiTiet.PhuCapDienThoaiCongNhat.Value + UpdatetinhLuongChiTiet.TienNghiLeNghiPhepCheDo.Value + UpdatetinhLuongChiTiet.TienHuongNgungLamViec.Value + UpdatetinhLuongChiTiet.TroCapThoiViec.Value + UpdatetinhLuongChiTiet.SoTienThuongKmCongNhat.Value + UpdatetinhLuongChiTiet.SoTienThuongChuyenCongNhat.Value + UpdatetinhLuongChiTiet.SoTienThuongHieuQuaCongViec.Value + UpdatetinhLuongChiTiet.DieuChinh.Value);
                            //end
                            // khấu trừ
                            UpdatetinhLuongChiTiet.TongKhauTru = (UpdatetinhLuongChiTiet.SoTienThuongChuyenCongNhat.Value + UpdatetinhLuongChiTiet.TienDongBaoHiemYTe.Value + UpdatetinhLuongChiTiet.TienDongBaoHiemXaHoi.Value + UpdatetinhLuongChiTiet.TienDongBaoHiemThatNghiep.Value + TienThueThuNhap + UpdatetinhLuongChiTiet.TienTamUng.Value);
                            //end

                            UpdatetinhLuongChiTiet.UpdateBy = Convert.ToInt32(HttpContext.Current.Session["USERID"]);
                            UpdatetinhLuongChiTiet.UpdateDate = DateTime.Now;
                            _DBTinhLuong.SubmitChanges();
                        }
                        catch (Exception) { }
                    }
                    _DBTinhLuong.SubmitChanges();
                    transaction.Complete();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
            
        }

        public CC_ChamCongNgayNhanVien GetBangChamCongById(int IdNhanVien,int Thang,int Nam)
        {
            try
            {
                var query = from bangchamcong in _DBTinhLuong.CC_BangChamCongs
                            join
                                chamcongngay in _DBTinhLuong.CC_ChamCongNgayNhanViens on bangchamcong.ID equals chamcongngay.MaBangChamCong
                            where bangchamcong.Thang == Thang && bangchamcong.Nam == Nam && chamcongngay.MaNhanVien == IdNhanVien
                            select new
                            {
                                chamcongngay
                            };
                return query.First().chamcongngay;
            }
            catch (Exception) { return null; }
            
        }
        #endregion

        #region tính toán lại dữ liệu cho một comlum

        public void TinhToanVaLuuLaiDuLieuChoMotCot(ref tb_TinhLuongThangChiTiet obj)
        {
            #region khai báo biến
            decimal tongngaylamviec, luongcanban, tienmotngaycong, LuongCongNhat, songayhuongcacphucap, tienchuyencan, LuongTruocThue, TienThueThuNhap, tienthuclanh, tongtiennhanduoc;
            decimal phucaptiencom, phucapdilai, phucaptrachnhiem, phucapdienthoai;
            //decimal TienNghiLeNghiPhepCheDo, SoTienThuongKmCongNhat, SoTienThuongChuyenCongNhat, SoTienThuongHieuQuaCongViec, DieuChinh, TienDongBaoHiemYTe, TienDongBaoHiemXaHoi, TienDongBaoHiemThatNghiep;
            //decimal TienTamUng, TienHuongNgungLamViec, TroCapThoiViec, TienPhiCongDoan, PCTrachNhiemCongNhat, ChuyenCanCongNhat, PhuCapDiLaiCongNhat, PhuCapDienThoaiCongNhat, PhuCapTienComCongNhat;
            int songaynghiphepnamchedoi;

            #endregion
            tongngaylamviec = obj.NgayCongLamViec!=null?obj.NgayCongLamViec.Value:0;
            luongcanban = obj.LuongCanBan != null ? obj.LuongCanBan.Value : 0;
            tienmotngaycong = luongcanban / 26;
            LuongCongNhat = (tienmotngaycong * tongngaylamviec);
            obj.LuongCongNhat = LuongCongNhat;
            songayhuongcacphucap = obj.NgayTinhPhuCap!=null?obj.NgayTinhPhuCap.Value:0;
            //trach nhiem
            phucaptrachnhiem = obj.PhucCapTrachNhiem!=null?obj.PhucCapTrachNhiem.Value:0;
            obj.PCTrachNhiemCongNhat = (phucaptrachnhiem / 26) * songayhuongcacphucap;//obj.PhucCapTrachNhiem;
            //chuyen cần
            tienchuyencan = obj.ChuyenCan!=null?obj.ChuyenCan.Value:0;
            obj.ChuyenCanCongNhat = (tienchuyencan / 26) * songayhuongcacphucap;// obj.ChuyenCan;
            // đi lại
            phucapdilai = obj.PhuCapDiLai != null ? obj.PhuCapDiLai.Value : 0;
            obj.PhuCapDiLaiCongNhat = (phucapdilai / 26) * songayhuongcacphucap; //obj.PhuCapDiLai;
            //tiền cơm
            phucaptiencom = obj.PhuCapTienCom!=null?obj.PhuCapTienCom.Value:0;
            obj.PhuCapTienComCongNhat = (phucaptiencom / 26) * songayhuongcacphucap;//obj.PhuCapTienCom;
            //tiền điện thoại
            phucapdienthoai =obj.PhuCapDienThoai!=null?obj.PhuCapDienThoai.Value:0;
            obj.PhuCapDienThoaiCongNhat = (phucapdienthoai / 26) * songayhuongcacphucap;//obj.PhuCapDienThoai;
            // nghỉ phép chế độ
            songaynghiphepnamchedoi = obj.NghiLeNghiPhepCheDo!=null?obj.NghiLeNghiPhepCheDo.Value:0;
            obj.TienNghiLeNghiPhepCheDo = this.GetTienNghiLe(songaynghiphepnamchedoi, tienmotngaycong);
            //thu nhập trước thuế
            LuongTruocThue = (obj.LuongCongNhat.Value + obj.PCTrachNhiemCongNhat.Value + obj.ChuyenCanCongNhat.Value + obj.PhuCapDiLaiCongNhat.Value + obj.PhuCapTienComCongNhat.Value + obj.PhuCapDienThoaiCongNhat.Value + obj.TienNghiLeNghiPhepCheDo.Value + obj.TienHuongNgungLamViec.Value + obj.TroCapThoiViec.Value + obj.SoTienThuongKmCongNhat.Value + obj.SoTienThuongChuyenCongNhat.Value + obj.SoTienThuongHieuQuaCongViec.Value + obj.DieuChinh.Value) - (obj.TienDongBaoHiemYTe.Value + obj.TienDongBaoHiemXaHoi.Value + obj.TienDongBaoHiemThatNghiep.Value + obj.TienPhiCongDoan.Value);
            obj.TienThuNhapTruocThue = LuongTruocThue;
            //end
            //tiền thuế thu nhập
            TienThueThuNhap = LuongTruocThue * (decimal)0.05;
            obj.TienThueThuNhap = TienThueThuNhap;
            //end
            //tiền thực lãnh
            tienthuclanh = LuongTruocThue - TienThueThuNhap;
            obj.TienThucLanh = tienthuclanh;
            //end
            //tổng tiền nhập được
            tongtiennhanduoc = tienthuclanh - obj.TienTamUng.Value;
            obj.TongTienNhanDuoc = tongtiennhanduoc;
            //end
            obj.UpdateBy = Convert.ToInt32(HttpContext.Current.Session["USERID"]);
            obj.UpdateDate = DateTime.Now;
            
        }

        #endregion

        public tb_DanhSachBangTinhLuong LuuBangTinhLuongVaLapBangTinhLuongChiTiet(tb_DanhSachBangTinhLuong DanhSachBangTinhLuong)
        {
            try
            {
                var query = from bangchamcong in _DBTinhLuong.CC_BangChamCongs
                            join
                                chamcongngay in _DBTinhLuong.CC_ChamCongNgayNhanViens on bangchamcong.ID equals chamcongngay.MaBangChamCong
                            where bangchamcong.Thang == DanhSachBangTinhLuong.Thang && bangchamcong.Nam == DanhSachBangTinhLuong.Nam
                            select new
                            {
                                bangchamcong,
                                chamcongngay
                            };
                var mucchiuthue = from mucchuithue in _DBTinhLuong.dm_MucChuiThues
                                  select new
                                  {
                                      mucchuithue.SoTienMucChiuThue
                                  };
                var phantramchiuthue = from phatramchiu in _DBTinhLuong.dm_ThueThuNhapCaNhans
                                       select new
                                       {
                                           phatramchiu.Tu,
                                           phatramchiu.Den,
                                           phatramchiu.Max,
                                           phatramchiu.Min,
                                           phatramchiu.PhanTramChiuThue
                                       };
                dm_BaoHiem BaoHiem = this.BaoHiem();
                decimal PhanTramBHYT = BaoHiem != null ? BaoHiem.BHYT.Value : 0;
                decimal PhanTramBHXH = BaoHiem != null ? BaoHiem.BHXH.Value : 0;
                decimal PhanTramBHTN = BaoHiem != null ? BaoHiem.BHTN.Value : 0;
                using (var transaction = new System.Transactions.TransactionScope())
                {
                    #region khai báo biến
                    decimal tongngaylamviec, luongcanban, tienmotngaycong, songayhuongcacphucap, tienchuyencan;
                    decimal phucaptiencom, phucapdilai, phucaptrachnhiem, phucapdienthoai, LuongTruocThue, TienThueThuNhap, tienthuclanh, tongtiennhanduoc, SoTienNguoiPhuThuoc;
                    //decimal TienNghiLeNghiPhepCheDo, SoTienThuongKmCongNhat, SoTienThuongChuyenCongNhat, SoTienThuongHieuQuaCongViec, DieuChinh, TienDongBaoHiemYTe, TienDongBaoHiemXaHoi, TienDongBaoHiemThatNghiep;
                    //decimal LuongCongNhat, PCTrachNhiemCongNhat, PhuCapTienComCongNhat, PhuCapDienThoaiCongNhat, PhuCapDiLaiCongNhat, ChuyenCanCongNhat, TienTamUng, TienHuongNgungLamViec, TroCapThoiViec, TienPhiCongDoan;
                    #endregion

                    foreach (var item in query)
                    {
                        tb_TinhLuongThangChiTiet tinhLuongChiTiet = new tb_TinhLuongThangChiTiet();
                        tinhLuongChiTiet.IdBangChamCong = item.bangchamcong.ID;
                        tinhLuongChiTiet.MaNhanVien = item.chamcongngay.MaNhanVien;
                        tinhLuongChiTiet.IdBangTinhLuong = DanhSachBangTinhLuong.ID;
                        // người phụ thuộc
                        SoTienNguoiPhuThuoc = 0;// nhập
                        tinhLuongChiTiet.SoTienNguoiPhuThuoc = 0;//nhập
                        //luong ban bản
                        tongngaylamviec = item.chamcongngay.TongSoNgayLamViec;
                        luongcanban = this.GetLuongCanBanCuaNhanVien(item.chamcongngay.MaNhanVien);
                        tienmotngaycong = luongcanban / 26;
                        tinhLuongChiTiet.NgayCongLamViec = tongngaylamviec;
                        tinhLuongChiTiet.LuongCanBan = luongcanban;
                        tinhLuongChiTiet.LuongCongNhat = tienmotngaycong * tongngaylamviec;//tinhLuongChiTiet.LuongCanBan;
                        songayhuongcacphucap = item.chamcongngay.TongSoNgayLamViec + item.chamcongngay.TongNghiLe + item.chamcongngay.TongNghiPhepNam + item.chamcongngay.TongNghiCheDo;
                        tinhLuongChiTiet.NgayTinhPhuCap = songayhuongcacphucap;
                        //trach nhiem
                        phucaptrachnhiem = this.GetTienPhuCapTrachNhiem(item.chamcongngay.MaNhanVien);
                        tinhLuongChiTiet.PhucCapTrachNhiem = phucaptrachnhiem;
                        tinhLuongChiTiet.PCTrachNhiemCongNhat = (phucaptrachnhiem / 26) * songayhuongcacphucap;//tinhLuongChiTiet.PhucCapTrachNhiem;
                        //chuyen cần
                        tienchuyencan = this.GetTienChuyenCan(item.chamcongngay.MaNhanVien);
                        tinhLuongChiTiet.ChuyenCan = tienchuyencan;
                        tinhLuongChiTiet.ChuyenCanCongNhat = (tienchuyencan / 26) * songayhuongcacphucap;// tinhLuongChiTiet.ChuyenCan;
                        // đi lại
                        phucapdilai = this.GetPhuCapDiLai(item.chamcongngay.MaNhanVien);
                        tinhLuongChiTiet.PhuCapDiLai = phucapdilai;
                        tinhLuongChiTiet.PhuCapDiLaiCongNhat = (phucapdilai / 26) * songayhuongcacphucap; //tinhLuongChiTiet.PhuCapDiLai;
                        //tiền cơm
                        phucaptiencom = this.GetPhuCapTienCom(item.chamcongngay.MaNhanVien);
                        tinhLuongChiTiet.PhuCapTienCom = phucaptiencom;
                        tinhLuongChiTiet.PhuCapTienComCongNhat = (phucaptiencom / 26) * songayhuongcacphucap;//tinhLuongChiTiet.PhuCapTienCom;
                        //tiền điện thoại
                        phucapdienthoai = this.GetPhuCapDienThoai(item.chamcongngay.MaNhanVien);
                        tinhLuongChiTiet.PhuCapDienThoai = phucapdienthoai;
                        tinhLuongChiTiet.PhuCapDienThoaiCongNhat = (phucapdienthoai / 26) * songayhuongcacphucap;//tinhLuongChiTiet.PhuCapDienThoai;
                        // nghỉ phép chế độ
                        int songaynghiphepnamchedoi = Convert.ToInt32(item.chamcongngay.TongNghiLe + item.chamcongngay.TongNghiCheDo + item.chamcongngay.TongNghiPhepNam);
                        tinhLuongChiTiet.NghiLeNghiPhepCheDo = songaynghiphepnamchedoi;
                        tinhLuongChiTiet.TienNghiLeNghiPhepCheDo = this.GetTienNghiLe(songaynghiphepnamchedoi, tienmotngaycong);

                        tinhLuongChiTiet.IdThuongKm = 0;
                        tinhLuongChiTiet.SoTienThuongKmCongNhat = 0;//chưa biết sao
                        tinhLuongChiTiet.SoTienThuongChuyenCongNhat = this.GetTienThuongChuyen(item.chamcongngay.MaNhanVien);

                        tinhLuongChiTiet.SoTienThuongHieuQuaCongViec = 0;//nhập
                        tinhLuongChiTiet.DieuChinh = 0;//nhập

                        tinhLuongChiTiet.TienDongBaoHiemYTe = luongcanban * PhanTramBHYT;
                        tinhLuongChiTiet.TienDongBaoHiemXaHoi = luongcanban * PhanTramBHXH;
                        tinhLuongChiTiet.TienDongBaoHiemThatNghiep = luongcanban * PhanTramBHTN;
                        tinhLuongChiTiet.TienTamUng = this.TongTienTamUngCuaNhanVien(tinhLuongChiTiet.MaNhanVien, DanhSachBangTinhLuong.Thang, DanhSachBangTinhLuong.Nam);

                        // thôi việc
                        tinhLuongChiTiet.TienHuongNgungLamViec = 0;//chưa xác định
                        tinhLuongChiTiet.TroCapThoiViec = 0;//nhập
                        tinhLuongChiTiet.TienPhiCongDoan = 0;//nhập

                        //thu nhập trước thuế
                        LuongTruocThue = (tinhLuongChiTiet.LuongCongNhat.Value + tinhLuongChiTiet.PCTrachNhiemCongNhat.Value + tinhLuongChiTiet.ChuyenCanCongNhat.Value + tinhLuongChiTiet.PhuCapDiLaiCongNhat.Value + tinhLuongChiTiet.PhuCapTienComCongNhat.Value + tinhLuongChiTiet.PhuCapDienThoaiCongNhat.Value + tinhLuongChiTiet.TienNghiLeNghiPhepCheDo.Value + tinhLuongChiTiet.TienHuongNgungLamViec.Value + tinhLuongChiTiet.TroCapThoiViec.Value + tinhLuongChiTiet.SoTienThuongKmCongNhat.Value + tinhLuongChiTiet.SoTienThuongChuyenCongNhat.Value + tinhLuongChiTiet.SoTienThuongHieuQuaCongViec.Value + tinhLuongChiTiet.DieuChinh.Value) - (tinhLuongChiTiet.TienDongBaoHiemYTe.Value + tinhLuongChiTiet.TienDongBaoHiemXaHoi.Value + tinhLuongChiTiet.TienDongBaoHiemThatNghiep.Value + tinhLuongChiTiet.TienPhiCongDoan.Value + tinhLuongChiTiet.SoTienNguoiPhuThuoc.Value);
                        tinhLuongChiTiet.TienThuNhapTruocThue = LuongTruocThue;
                        //end
                        //tiền thuế thu nhập
                        if (LuongTruocThue > 0)
                        {
                            decimal SoTienChiuThue = LuongTruocThue - (mucchiuthue.First().SoTienMucChiuThue != null ? mucchiuthue.First().SoTienMucChiuThue.Value : 0);
                            if (SoTienChiuThue > 0)
                            {
                                if (SoTienChiuThue < 5000000)
                                    TienThueThuNhap = SoTienChiuThue * (decimal)0.05;
                                else if (SoTienChiuThue < 10000000)
                                    TienThueThuNhap = SoTienChiuThue * (decimal)0.1 - (decimal)(0.05 * 5000000);
                                else if (SoTienChiuThue < 18000000)
                                    TienThueThuNhap = SoTienChiuThue * (decimal)0.15 - (decimal)(0.1 * 5000000 + 0.05 * 5000000);
                                else if (SoTienChiuThue < 32000000)
                                    TienThueThuNhap = SoTienChiuThue * (decimal)0.2 - (decimal)(0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                                else if (SoTienChiuThue < 52000000)
                                    TienThueThuNhap = SoTienChiuThue * (decimal)0.25 - (decimal)(0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                                else if (SoTienChiuThue < 80000000)
                                    TienThueThuNhap = SoTienChiuThue * (decimal)0.3 - (decimal)(0.25 * 20000000 + 0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);
                                else TienThueThuNhap = SoTienChiuThue * (decimal)0.35 - (decimal)(0.3 * 28000000 + 0.25 * 20000000 + 0.2 * 14000000 + 0.15 * 8000000 + 0.1 * 5000000 + 0.05 * 5000000);

                            }
                            else TienThueThuNhap = 0;
                        }
                        else TienThueThuNhap = 0;
                        tinhLuongChiTiet.TienThueThuNhap = TienThueThuNhap;
                        //end
                        //tiền thực lãnh
                        if (LuongTruocThue < 0)
                            tienthuclanh = LuongTruocThue + SoTienNguoiPhuThuoc;
                        else
                            tienthuclanh = LuongTruocThue - TienThueThuNhap;
                        tinhLuongChiTiet.TienThucLanh = tienthuclanh;
                        //end
                        //tổng tiền nhập được
                        tongtiennhanduoc = tienthuclanh - tinhLuongChiTiet.TienTamUng.Value - tinhLuongChiTiet.SoTienThuongChuyenCongNhat.Value;
                        tinhLuongChiTiet.TongTienNhanDuoc = tongtiennhanduoc;
                        //end
                        //trả gộp
                        tinhLuongChiTiet.TienTraGop = (tinhLuongChiTiet.LuongCongNhat.Value + tinhLuongChiTiet.PCTrachNhiemCongNhat.Value + tinhLuongChiTiet.ChuyenCanCongNhat.Value + tinhLuongChiTiet.PhuCapDiLaiCongNhat.Value + tinhLuongChiTiet.PhuCapTienComCongNhat.Value + tinhLuongChiTiet.PhuCapDienThoaiCongNhat.Value + tinhLuongChiTiet.TienNghiLeNghiPhepCheDo.Value + tinhLuongChiTiet.TienHuongNgungLamViec.Value + tinhLuongChiTiet.TroCapThoiViec.Value + tinhLuongChiTiet.SoTienThuongKmCongNhat.Value + tinhLuongChiTiet.SoTienThuongChuyenCongNhat.Value + tinhLuongChiTiet.SoTienThuongHieuQuaCongViec.Value + tinhLuongChiTiet.DieuChinh.Value);
                        //end
                        // khấu trừ
                        tinhLuongChiTiet.TongKhauTru =(tinhLuongChiTiet.SoTienThuongChuyenCongNhat.Value+tinhLuongChiTiet.TienDongBaoHiemYTe.Value + tinhLuongChiTiet.TienDongBaoHiemXaHoi.Value + tinhLuongChiTiet.TienDongBaoHiemThatNghiep.Value+TienThueThuNhap+tinhLuongChiTiet.TienTamUng.Value);
                        //end
                        tinhLuongChiTiet.ThangTinhLuong = DanhSachBangTinhLuong.Thang;
                        tinhLuongChiTiet.NamTinhLuong = DanhSachBangTinhLuong.Nam;
                        tinhLuongChiTiet.CreateBy = Convert.ToInt32(HttpContext.Current.Session["USERID"]);
                        tinhLuongChiTiet.CreateDate = DateTime.Now;
                        tinhLuongChiTiet.UpdateBy = Convert.ToInt32(HttpContext.Current.Session["USERID"]);
                        tinhLuongChiTiet.UpdateDate = DateTime.Now;
                        tinhLuongChiTiet.IsDelete = false;
                        _DBTinhLuong.tb_TinhLuongThangChiTiets.InsertOnSubmit(tinhLuongChiTiet);
                    }
                    _DBTinhLuong.SubmitChanges();
                    transaction.Complete();
                    return DanhSachBangTinhLuong;
                }
            }
            catch (Exception) {
                return null;
            }
            
        }

        #region tiền tạm ứng
        public decimal TongTienTamUngCuaNhanVien(int MaNhanVien, int thang, int nam)
        {
            try {
                return _DBTinhLuong.tb_TamUngs.Where(p => p.IdNhanVien == MaNhanVien && p.ThangTamUng == thang && p.NamTamUng == nam && (p.IsDelete == false || p.IsDelete == null)).Sum(p => p.SoTien);
            }
            catch (Exception) {
                return 0;
            }
        }

        #endregion

        #region Lấy bảo hiểm

        public dm_BaoHiem BaoHiem()
        {
            try
            {
                return _DBTinhLuong.dm_BaoHiems.Where(p => p.IsActive == true).First();
            }
            catch (Exception) { return null; }
        }
        #endregion

        #region lương căn bản
        public decimal GetLuongCanBanCuaNhanVien(int IdNhanVien)
        {
            try
            {
                var query = from nhanvienluongcanban in _DBTinhLuong.hs_NhanVien_LuongCoBans
                            join luongcanban in _DBTinhLuong.dm_TienLuongCoBans on nhanvienluongcanban.IdLuongCoBan equals luongcanban.Id
                            where nhanvienluongcanban.IdNhanVien == IdNhanVien && nhanvienluongcanban.TrangThaiSuDung == true
                            select new
                            {
                                luongcanban.TienLuongCoBan
                            };
                return query.First().TienLuongCoBan.Value;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        #endregion

        #region phụ cấp trách nhiệm
        public decimal GetTienPhuCapTrachNhiem(int IdNhanVien)
        {
            try
            {
                var query = from phucaptrachnhiem in _DBTinhLuong.hs_NhanVien_TrachNhiems
                            join dmphucaptrachnhiem in _DBTinhLuong.dm_TrachNhiems on phucaptrachnhiem.IdTrachNhiem equals dmphucaptrachnhiem.Id
                            where phucaptrachnhiem.IdNhanVien == IdNhanVien && phucaptrachnhiem.TrangThaiSuDung == true
                            select new
                            {
                                dmphucaptrachnhiem.TienTrachNhiem
                            };
                return query.First().TienTrachNhiem.Value;
            }
            catch (Exception)
            {
                return 0;
            }
        }

        #endregion

        #region phụ cấp chuyên cần
        public decimal GetTienChuyenCan(int IdNhanVien)
        {
            try
            {
                var query = from tienchuyencan in _DBTinhLuong.hs_NhanVien_PCChuyenCans
                            join dmtienchuyencan in _DBTinhLuong.dm_ChuyenCans on tienchuyencan.IdPCChuyenCan equals dmtienchuyencan.Id
                            where tienchuyencan.IdNhanVien == IdNhanVien && tienchuyencan.TrangThaiSuDung == true
                            select new
                            {
                                dmtienchuyencan.TienPhuCapChuyenCan
                            };
                return query.First().TienPhuCapChuyenCan.Value;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        #endregion

        #region phục cấp đi lại
        public decimal GetPhuCapDiLai(int IdNhanVien)
        {
            try
            {
                var query = from tiendilai in _DBTinhLuong.hs_NhanVien_PCDiLais
                            join dmphucapdilai in _DBTinhLuong.dm_PhuCapDiLais on tiendilai.IdPCDiLai equals dmphucapdilai.Id
                            where tiendilai.IdNhanVien == IdNhanVien && tiendilai.TrangThaiSuDung == true
                            select new
                            {
                                dmphucapdilai.TienPhuCapDiLai
                            };
                return query.First().TienPhuCapDiLai.Value;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        #endregion

        #region phụ cấp tiền cơm
        public decimal GetPhuCapTienCom(int IdNhanVien)
        {
            try
            {
                var query = from tiencom in _DBTinhLuong.hs_NhanVien_PCComs
                            join dmtiencom in _DBTinhLuong.dm_TienComs on tiencom.IdPCCom equals dmtiencom.Id
                            where tiencom.IdNhanVien == IdNhanVien && tiencom.TrangThaiSuDung == true
                            select new
                            {
                                dmtiencom.TienCom
                            };
                return query.First().TienCom.Value;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        #endregion

        #region phụ cấp tiền điện thoại
        public decimal GetPhuCapDienThoai(int IdNhanVien)
        {
            try
            {
                var query = from dienthoai in _DBTinhLuong.hs_NhanVien_PCDienThoais
                            join dmdienthoai in _DBTinhLuong.dm_PCDienThoais on dienthoai.IdPCDienThoai equals dmdienthoai.Id
                            where dienthoai.IdNhanVien == IdNhanVien && dienthoai.TrangThaiSuDung == true
                            select new
                            {
                                dmdienthoai.TienPCDienThoai
                            };
                return query.First().TienPCDienThoai.Value;
            }
            catch (Exception)
            {
                return 0;
            }
        }
        #endregion

        #region tính tiền thưởng chuyến
        
        public decimal GetTienThuongChuyen(int MaNhanVien)
        {
            try
            {
                return _DBTinhLuong.tb_TinhLuongThang_ThuongKmTrongThangs.Where(p =>p.MaNhanVien == MaNhanVien ).Sum(p =>p.SoTienNhanDuocTrongChuyen??0);
            }
            catch (Exception)
            {
                return 0;
            }
        }
        #endregion

        #region số tiền tính nghỉ lễ

        public decimal GetTienNghiLe(int songay,decimal luongmotngay)
        {
            try {
                int phantramtinhluong =  _DBTinhLuong.CC_KyHieuChamCongs.SingleOrDefault(p => p.ID.Equals("L")).PhanTramCuaLuong.Value;// L ký hiêu là lễ
                decimal sotien = (luongmotngay * songay * phantramtinhluong) / 100;
                return sotien;
            }
            catch (Exception) { return 0; }
        }
        #endregion

        #region load dữ liệu tổng hợp theo tháng để tính lương

        public void TongHopDuLieuCacBangTinhLuong(ref TinhLuongDto TinhLuongDto, tb_TinhLuongThangChiTiet TinhLuongChiTiet)
        {
            var query = from bangchamcong in _DBTinhLuong.CC_BangChamCongs
                        join
                            chamcongngay in _DBTinhLuong.CC_ChamCongNgayNhanViens on bangchamcong.ID equals chamcongngay.MaBangChamCong
                        where bangchamcong.Thang == TinhLuongChiTiet.ThangTinhLuong && bangchamcong.Nam == TinhLuongChiTiet.NamTinhLuong && chamcongngay.MaNhanVien == TinhLuongChiTiet.MaNhanVien
                        orderby chamcongngay.NgayTao descending
                        select new
                        {
                            bangchamcong,
                            chamcongngay
                        };
            TinhLuongDto.IdBangChamCong = query.First().bangchamcong.ID;
            TinhLuongDto.MaNhanVien = query.First().chamcongngay.MaNhanVien;
            TinhLuongDto.IdBangTinhLuong = TinhLuongChiTiet.IdBangTinhLuong;
            //luong ban bản
            decimal tongngaylamviec = query.First().chamcongngay.TongSoNgayLamViec;
            decimal luongcanban = this.GetLuongCanBanCuaNhanVien(query.First().chamcongngay.MaNhanVien);
            decimal tienmotngaycong = luongcanban / 26;
            TinhLuongDto.NgayCongLamViec = tongngaylamviec.ToString("#,#0").Replace(".", ",");
            TinhLuongDto.LuongCanBan = luongcanban.ToString("#,#0").Replace(",", ".");
            decimal LuongCongNhat = (tienmotngaycong * tongngaylamviec);
            TinhLuongDto.LuongCongNhat = LuongCongNhat.ToString("#,#0").Replace(",", ".");//tinhLuongChiTiet.LuongCanBan;

            decimal songayhuongcacphucap = query.First().chamcongngay.TongSoNgayLamViec + query.First().chamcongngay.TongNghiLe + query.First().chamcongngay.TongNghiPhepNam + query.First().chamcongngay.TongNghiCheDo;
            TinhLuongDto.NgayTinhPhuCap = songayhuongcacphucap.ToString("#,#0").Replace(",", "."); ;
            //trach nhiem
            decimal phucaptrachnhiem = this.GetTienPhuCapTrachNhiem(query.First().chamcongngay.MaNhanVien);
            TinhLuongDto.PhuCapTrachNhiem = phucaptrachnhiem.ToString("#,#0").Replace(",", ".");
            decimal PCTrachNhiemCongNhat = ((phucaptrachnhiem / 26) * songayhuongcacphucap);
            TinhLuongDto.PCTrachNhiemCongNhat = PCTrachNhiemCongNhat.ToString("#,#0").Replace(",", ".");//tinhLuongChiTiet.PhucCapTrachNhiem;
            //chuyen cần
            decimal tienchuyencan = this.GetTienChuyenCan(query.First().chamcongngay.MaNhanVien);
            TinhLuongDto.ChuyenCan = tienchuyencan.ToString("#,#0").Replace(",", ".");
            decimal ChuyenCanCongNhat = ((tienchuyencan / 26) * songayhuongcacphucap);
            TinhLuongDto.ChuyenCanCongNhat = ChuyenCanCongNhat.ToString("#,#0").Replace(",", ".");// tinhLuongChiTiet.ChuyenCan;
            // đi lại
            decimal phucapdilai = this.GetPhuCapDiLai(query.First().chamcongngay.MaNhanVien);
            TinhLuongDto.PhuCapDiLai = phucapdilai.ToString("#,#0").Replace(",", ".");
            decimal PhuCapDiLaiCongNhat = ((phucapdilai / 26) * songayhuongcacphucap);
            TinhLuongDto.PhuCapDiLaiCongNhat = PhuCapDiLaiCongNhat.ToString("#,#0").Replace(",", "."); //tinhLuongChiTiet.PhuCapDiLai;
            //tiền cơm
            decimal phucaptiencom = this.GetPhuCapTienCom(query.First().chamcongngay.MaNhanVien);
            TinhLuongDto.PhuCapTienCom = phucaptiencom.ToString("#,#0").Replace(",", ".");
            decimal PhuCapTienComCongNhat = ((phucaptiencom / 26) * songayhuongcacphucap);
            TinhLuongDto.PhuCapTienComCongNhat = PhuCapTienComCongNhat.ToString("#,#0").Replace(",", ".");//tinhLuongChiTiet.PhuCapTienCom;
            //tiền điện thoại
            decimal phucapdienthoai = this.GetPhuCapDienThoai(query.First().chamcongngay.MaNhanVien);
            TinhLuongDto.PhuCapDienThoai = phucapdienthoai.ToString("#,#0").Replace(",", ".");
            decimal PhuCapDienThoaiCongNhat = ((phucapdienthoai / 26) * songayhuongcacphucap);
            TinhLuongDto.PhuCapDienThoaiCongNhat = PhuCapDienThoaiCongNhat.ToString("#,#0").Replace(",", ".");//tinhLuongChiTiet.PhuCapDienThoai;
            // nghỉ phép chế độ
            int songaynghiphepnamchedoi = Convert.ToInt32(query.First().chamcongngay.TongNghiLe + query.First().chamcongngay.TongNghiCheDo + query.First().chamcongngay.TongNghiPhepNam);
            TinhLuongDto.NghiLeNghiPhepCheDo = songaynghiphepnamchedoi;
            decimal TienNghiLeNghiPhepCheDo = this.GetTienNghiLe(songaynghiphepnamchedoi, tienmotngaycong);
            TinhLuongDto.TienNghiLeNghiPhepCheDo = TienNghiLeNghiPhepCheDo.ToString("#,#0").Replace(",", ".");

            TinhLuongDto.IdThuongKm = 0;
            decimal SoTienThuongKmCongNhat = 0;
            TinhLuongDto.SoTienThuongKmCongNhat = SoTienThuongKmCongNhat.ToString("#,#0").Replace(",",".");
            decimal SoTienThuongChuyenCongNhat = this.GetTienThuongChuyen(query.First().chamcongngay.MaNhanVien);
            TinhLuongDto.SoTienThuongChuyenCongNhat = SoTienThuongChuyenCongNhat.ToString("#,#0").Replace(",", ".");

            decimal SoTienThuongHieuQuaCongViec = 0;
            TinhLuongDto.SoTienThuongHieuQuaCongViec = SoTienThuongHieuQuaCongViec.ToString("#,#0").Replace(",",".");//nhập
            decimal DieuChinh = 0;
            TinhLuongDto.DieuChinh = DieuChinh.ToString("#,#0").Replace(",", ".");

            decimal TienDongBaoHiemYTe=0;
            TinhLuongDto.TienDongBaoHiemYTe = TienDongBaoHiemYTe.ToString("#,#0").Replace(",", ".");
            
            decimal TienDongBaoHiemXaHoi =0;
            TinhLuongDto.TienDongBaoHiemXaHoi = TienDongBaoHiemXaHoi.ToString("#,#0").Replace(",",".");
            decimal TienDongBaoHiemThatNghiep = 0;
            TinhLuongDto.TienDongBaoHiemThatNghiep = TienDongBaoHiemThatNghiep.ToString("#,#0").Replace(",",".");
            decimal TienTamUng = 0;
            TinhLuongDto.TienTamUng = TienTamUng.ToString("#,#0").Replace(",", ".");
            // thôi việc
            decimal TienHuongNgungLamViec = 0;
            decimal TroCapThoiViec = 0;
            decimal TienPhiCongDoan = 0;
            //thu nhập trước thuế
            decimal LuongTruocThue = (LuongCongNhat + PCTrachNhiemCongNhat + ChuyenCanCongNhat + PhuCapDiLaiCongNhat + PhuCapTienComCongNhat + PhuCapDienThoaiCongNhat + TienNghiLeNghiPhepCheDo + TienHuongNgungLamViec + TroCapThoiViec + SoTienThuongKmCongNhat + SoTienThuongChuyenCongNhat + SoTienThuongHieuQuaCongViec + DieuChinh) - (TienDongBaoHiemYTe + TienDongBaoHiemXaHoi+ TienDongBaoHiemThatNghiep + TienPhiCongDoan);
            TinhLuongDto.TienThuNhapTruocThue = LuongTruocThue.ToString("#,#0").Replace(",", ".");
            //end
            //tiền thuế thu nhập
            decimal TienThueThuNhap = LuongTruocThue * (decimal)0.05;
            TinhLuongDto.TienThueThuNhap = TienThueThuNhap.ToString("#,#0").Replace(",", ".");
            //end
            //tiền thực lãnh
            decimal tienthuclanh = LuongTruocThue - TienThueThuNhap;
            TinhLuongDto.TienThucLanh = tienthuclanh.ToString("#,#0").Replace(",", ".");
            //end
            //tổng tiền nhập được
            decimal tongtiennhanduoc = tienthuclanh - TienTamUng;
            TinhLuongDto.TongTienNhanDuoc = tongtiennhanduoc.ToString("#,#0").Replace(",", ".");
            //end
            TinhLuongDto.ThangTinhLuong = TinhLuongChiTiet.ThangTinhLuong != null ? TinhLuongChiTiet.ThangTinhLuong.Value : 0;
            TinhLuongDto.NamTinhLuong = TinhLuongChiTiet.NamTinhLuong != null ? TinhLuongChiTiet.NamTinhLuong.Value : 0;
            TinhLuongDto.IsFinish = TinhLuongChiTiet.IsFinish != null ? TinhLuongChiTiet.IsFinish.Value : false;
            TinhLuongDto.IsLock = TinhLuongChiTiet.IsLock != null ? TinhLuongChiTiet.IsLock.Value : false;
            TinhLuongDto.IsDelete = TinhLuongChiTiet.IsDelete;
        }

        #endregion
        
        #endregion


        #region BACNP

        public IList<tb_TinhLuongThangChiTiet> ReportGetAllTinhLuongByIdBangTinhLuong(int idBangTinhLuong)
        {
            return _DBTinhLuong.tb_TinhLuongThangChiTiets.Where(p => p.IdBangTinhLuong == idBangTinhLuong && (p.IsDelete == false || p.IsDelete == null)).ToList();
        }

        public dm_BaoHiem ReportGetBaoHiemById(int idBaoHiem)
        {
            return _DBTinhLuong.dm_BaoHiems.SingleOrDefault(p => p.ID == idBaoHiem);
        }

        #endregion
    }
}